/* Trigger Example: * Updates one table from the contents of another table. * This could be useful if you have 2 different applications that require different tables but the data must be tied together. * Also notice that you can determine what the prior value was for updates from the DELETED table. The current key for this table is the "logonid". See the problem with changing the key. Look for "ChangeMe" and "ChangeCompleted" for the example. Notice: mytableapp1's state value is a char and mytableapp2's state is an int. SQL To remove all tables & triggers created: drop table mytableapp1 drop table mytableapp2 drop table mytableload1 drop trigger mytableapp2_ins_del_upd */ set nocount on if exists (select * from [dbo].[sysobjects] where id = object_id(N'mytableapp1') ) begin drop table mytableapp1 end go create table mytableapp1 ( logonid varchar(20), statechar char(1) ) go if exists (select * from [dbo].[sysobjects] where id = object_id(N'mytableapp2') ) begin drop table mytableapp2 end go create table mytableapp2 ( logonid varchar(20), stateint int, updatenote nvarchar(255) ) go /* ***************************** Trigger - Insert, Delete, Update ***************************** */ if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[mytableapp2_ins_del_upd]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) begin drop trigger mytableapp2_ins_del_upd end go create trigger [mytableapp2_ins_del_upd] ON [dbo].[mytableapp1] for insert, update, delete as begin /* Description: Will Insert, Delete, Update mytableapp2 table based on changes to mytableapp1. */ set nocount on if ( (select trigger_nestlevel() ) = 1 ) begin declare @delrecs int declare @insrecs int set @insrecs = (select count(*) from INSERTED) set @delrecs = (select count(*) from DELETED) if ( @insrecs > 0 and @delrecs > 0 ) begin -- **** Updates **** declare @intCountIns int set @intCountIns = (select count(*) from INSERTED) print 'Update occurred - recs = ' + convert(nvarchar, @intCountIns) if update( logonid ) begin print 'Updated logonid column. This SQL code cannot update the other table (app2) in this case because the primary key has changed.' end update app2 set stateint = (case ins.statechar when 'A' then 1 else 0 end), updatenote = 'App1''s statechar = ' + del.statechar from mytableapp2 app2 left join INSERTED ins on ins.logonid = app2.logonid left join DELETED del on del.logonid = app2.logonid where app2.logonid = ins.logonid /* update mytableapp2 set stateint = (case ins.statechar when 'A' then 1 else 0 end) from INSERTED ins where ins.logonid = mytableapp2.logonid */ end else if ( @insrecs > 0 ) begin -- **** Inserts **** print 'Insert occurred' insert into mytableapp2 (logonid, stateint) select distinct logonid, (case statechar when 'A' then 1 else 0 end) from inserted end else if ( @delrecs > 0 ) begin -- **** Deletes **** print 'Delete occurred' -- Warning: Becareful with the deletes!!! delete from mytableapp2 from DELETED where mytableapp2.logonid = DELETED.logonid end end end GO -- *************************** -- End Trigger -- *************************** -- **************************** -- Example of inserts. -- **************************** print 'Inserted User10,11,20,21' insert into mytableapp1 (logonid, statechar) values ('User10','A') insert into mytableapp1 (logonid, statechar) values ('User11','A') insert into mytableapp1 (logonid, statechar) values ('User20','A') insert into mytableapp1 (logonid, statechar) values ('User21','A') GO select app1.logonid as 'app1.logonid', app2.logonid as 'app2.logonid', app1.statechar as 'app1.statechar', app2.stateint as 'app2.stateint', app2.updatenote as 'app2.updatenote' from mytableapp1 app1 full join mytableapp2 app2 on app2.logonid = app1.logonid GO -- **************************** -- Example of updates. -- **************************** print 'Updated User20' update mytableapp1 set statechar = 'R' where logonid = 'User20' print 'Updated User21' update mytableapp1 set statechar = 'R' where logonid = 'User21' GO select app1.logonid as 'app1.logonid', app2.logonid as 'app2.logonid', app1.statechar as 'app1.statechar', app2.stateint as 'app2.stateint', app2.updatenote as 'app2.updatenote' from mytableapp1 app1 full join mytableapp2 app2 on app2.logonid = app1.logonid GO --Create a data table. if exists (select * from [dbo].[sysobjects] where id = object_id(N'mytableload1') ) begin drop table mytableload1 end create table mytableload1 ( userid varchar(20), state char(1) ) insert into mytableload1 (userid, state) values ('User30','A') insert into mytableload1 (userid, state) values ('User31','A') insert into mytableload1 (userid, state) values ('User32','A') insert into mytableload1 (userid, state) values ('ChangeMe','X') insert into mytableload1 (userid, state) values ('DeleteMe','A') insert into mytableload1 (userid, state) values ('DeleteMeBatch1','A') insert into mytableload1 (userid, state) values ('DeleteMeBatch2','A') insert into mytableload1 (userid, state) values ('DeleteMeBatch3','A') go -- **************************** -- Example of batch inserts. -- **************************** print 'Inserted User30-32,ChangeMe,DeleteMe,DeleteMeBatch1-3' insert into mytableapp1 (logonid, statechar) select userid, state from mytableload1 select app1.logonid as 'app1.logonid', app2.logonid as 'app2.logonid', app1.statechar as 'app1.statechar', app2.stateint as 'app2.stateint', app2.updatenote as 'app2.updatenote' from mytableapp1 app1 full join mytableapp2 app2 on app2.logonid = app1.logonid GO -- **************************** -- Example of batch updates. -- **************************** print 'Updated: User3%' update mytableapp1 set statechar = 'R' where logonid like 'User3%' GO select app1.logonid as 'app1.logonid', app2.logonid as 'app2.logonid', app1.statechar as 'app1.statechar', app2.stateint as 'app2.stateint', app2.updatenote as 'app2.updatenote' from mytableapp1 app1 full join mytableapp2 app2 on app2.logonid = app1.logonid GO print 'Updated: ChangeMe' update mytableapp1 set logonid = 'ChangeCompleted' where logonid = 'ChangeMe' GO select * from mytableapp1 select * from mytableapp2 select app1.logonid as 'app1.logonid', app2.logonid as 'app2.logonid', app1.statechar as 'app1.statechar', app2.stateint as 'app2.stateint', app2.updatenote as 'app2.updatenote' from mytableapp1 app1 full join mytableapp2 app2 on app2.logonid = app1.logonid GO select ( select count(*) from mytableapp1 ) as 'App1 count(*)', ( select count(*) from mytableapp2 ) as 'App2 count(*)' GO -- **************************** -- Example of Delete. -- **************************** print 'Deleted: DeleteMe' delete mytableapp1 where logonid = 'DeleteMe' GO select ( select count(*) from mytableapp1 ) as 'App1 count(*)', ( select count(*) from mytableapp2 ) as 'App2 count(*)' GO -- **************************** -- Example of Batch Deletes. -- **************************** print 'Deleted: DeleteMeBatch1-3' delete mytableapp1 where logonid like 'DeleteMeBatch%' GO select ( select count(*) from mytableapp1 ) as 'App1 count(*)', ( select count(*) from mytableapp2 ) as 'App2 count(*)' select app1.logonid as 'app1.logonid', app2.logonid as 'app2.logonid', app1.statechar as 'app1.statechar', app2.stateint as 'app2.stateint', app2.updatenote as 'app2.updatenote' from mytableapp1 app1 full join mytableapp2 app2 on app2.logonid = app1.logonid GO