There are some flags in Table1 that need to be updated ,,for instance if any
record from Table1 is inserted into Table2 ,the equivalent flag needs to be
set,or if it's ignored same thing must happen
thanks
[quoted text, click to view]

Have you ever used this? How is the performance?

Thanks for the reply

[quoted text, click to view]

I normally avoid using "instead of" triggers but it seems as a pretty good
way to solve your problem.
I use "instead of" triggers when I can't control the inserts (maybe from
other programs...) but want to log data inserted in my tables.
Converting rows to fit my tables constraints and log rows which do not
follow the constraints... this is usually bad practice because the inserts
should follow your table structure from the start. But sometimes you have no
choice.

I think the performance is pretty good because we use the primary keys all
the way.
But as always, the only way to know is to try :)
I don't know if there is som special performance issues regarding "instead
of" triggers but all you gurus out there certainly has something to say.

if the rule for a table is the same as the marking you can use the following
trigger. In this we only access the tables once, instead of twice as I did in
the previous, once for the rulecheck and once for the actual update of the
marking.
The downside is that it fires triggers in table1 and table3 even if no rows
are affected so us the "if @@rowcount != 0" in the beginning of each trigger
to ensure that no unnecessary sql is executed.


/Tobbe

create trigger EnforceRule on table2
instead of insert
as

if @@rowcount != 0 --Only trigger when rows are actually inserted.
@@rowcount contains the last batch (The actual insert statement)

--Rules for table1
update t1
set t1.checked = 1
from table1 t1
join inserted i
on i.clientid = t1.clientid
and i.studentid = t1.studentid

--Rules for table3
if @@Rowcount = 0
begin
update t3
set t3.checked = 1
from table3 t3
join inserted i
on i.internalid = t3.internalid

if @@Rowcount = 0
begin
insert table2
select *
from inserted
end
end


/*
--TEST
create table table1(clientid int, studentid int, checked int)
create table table2(clientid int, studentid int, internalid int)
create table table3(internalid int,checked int)

insert table1 values(1,1,0)
insert table3 values(1,0)

insert table2 values(1,1,1)
insert table2 values(2,1,1)
insert table2 values(2,1,2)

select * from table1
select * from table2
select * from table3
*/

[quoted text, click to view]