Determining Event Type in Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Determining Event Type in Trigger

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-05-27 : 11:29:27

I want to log changes to a table: Update, Insert and Delete. But I only want to write 1 Trigger instead of 3.

Is this a good way to determine which of the 3 (i.e. Update, Insert or Delete) is occuring from inside the Trigger:

case 
  when exists (select 1 from Inserted) and 
       exists (select 1 from Deleted) then 'Update' 
  when exists (select 1 from Deleted) then 'Delete' 
  else 'Insert'
end DML_Type

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 11:39:27

Yes.

Another logic would be

if exists(select 1 from Deleted) 
	if exists(select 1 from inserted) 
		select 'update'
	else
		select 'delete'
else
	select 'insert'

Madhivanan

Failing to plan is Planning to fail

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-05-27 : 11:45:05

Thankyou Madhivanan.

Also is there a way to determine the table name firing the trigger? Like with the EventData XML or could that be slow. That part is not too important - just wondering.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 12:00:47

quote:
Originally posted by denis_the_thief

Thankyou Madhivanan.

Also is there a way to determine the table name firing the trigger? Like with the EventData XML or could that be slow. That part is not too important - just wondering.

I dont think you can make use of EventData XML which is used for DDL trigger. But try it and see if it works

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources