Trigger updating another table

Rated 4.19/5 based on 624 customer reviews

Therefore, the first two statements in the trigger should look like it is shown below. Data2(Col int not null) go create trigger trg_Data_AI on dbo.Data2 after insert as select 'After Insert' as [Trigger] ,@@Row Count as [Row Count] ,(select count(*) from inserted) as [Inserted Cnt] ,(select count(*) from deleted) as [Deleted Cnt] go create trigger trg_Data_AU on dbo.They make sense when I'm dealing with an INSERT or DELETE operation, but how about an update?And do they contain a single row per row of data changed or are they truly tables? These two tables if they contain anything, contain a row for every row of data affected and therefore should be treated as tables.Data2 as Target using (select 1 as [Value]) as Source on Target. Value when not matched by target then insert(Col) values(Source.Value) when not matched by source then delete when matched then update set Col = Source.Too many times we see cursors going row-by-row through these tables when a set based operation would have been better.

trigger updating another table-12

trigger updating another table-72

trigger updating another table-72

However, it would always lead to unnecessary performance overhead. The code below creates another table and changes trigger in a way, that mimics behavior of audit functional, which is frequently implemented based on triggers. Audit ( ID int not null identity(1,1), On Date datetime2(0) not null constraint DEF_Audit_On Date default getutcdate(), Message varchar(64) not null, ) go alter trigger trg Data_AI on dbo.Data after insert as /* Some code */ declare @ID int set @ID = (select ID from inserted) /* Some code */ And there is another consequence of statement-level nature of triggers.They fire even if DML statement did not modify any rows.Data2 after update as select 'After Update' as [Trigger] ,@@Row Count as [Row Count] ,(select count(*) from inserted) as [Inserted Cnt] ,(select count(*) from deleted) as [Deleted Cnt] go create trigger trg_Data_AD on dbo.Data2 after delete as select 'After Delete' as [Trigger] ,@@Row Count as [Row Count] ,(select count(*) from inserted) as [Inserted Cnt] ,(select count(*) from deleted) as [Deleted Cnt] gomerge into dbo.

Leave a Reply