/*
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