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.
| Author |
Topic |
|
sujosh
Yak Posting Veteran
55 Posts |
 Posted - 2003-09-15 : 17:58:13
|
| I am writing a trigger insert/update/delete on a table. I want to do determine whether the trigger got invoked due to insert/update/delete.In Oracle I am using IF inserting/If updating/If deleting. What is its equivalent in Sql Server? I am trying to circumvent from writing 3 triggersThanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-15 : 18:03:54
|
| If you look at CREATE TRIGGER in SQL Server Books Online (SQL Server's manual), you'll see an IF UPDATE option. It can not be used for DELETEs, but once you put all of the different IF statements that you'll need, the last one could be used for the DELETE.Tara |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-15 : 18:05:40
|
| BTW, why do you want to circumvent writing 3 triggers?Tara |
|
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2003-09-15 : 18:17:22
|
| I have a history table that has several columns and one of them is a history description. The reason I would like to avoid If i could to have 3 triggers is for purely maintenance purposes.I need to track certain columns and insert into the history table when they get inserted/modified/deleted. So, I thought I could do it like the way I did it in Oracle. In Oracle I could do If Inserting/If Updating/If deleting in the same trigger contruct and under each "IF" I checked to see whether the columns old and new values have changed and so on and so forth....SO I thought I could do that same thing SQLServer.. Now IF UPDATE(col_name) will not solve my problem since I change the text depending on INSERT ( I input this value "a" has been inserted) and for UPDATE(I input this value has been modifed from value a to value b) and DELETE(I input the value b has been deleted) in the history description columnAny other ideasThanks |
|
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2003-09-15 : 18:18:07
|
| I have a history table that has several columns and one of them is a history description. The reason I would like to avoid If i could to have 3 triggers is for purely maintenance purposes.I need to track certain columns and insert into the history table when they get inserted/modified/deleted. So, I thought I could do it like the way I did it in Oracle. In Oracle I could do If Inserting/If Updating/If deleting in the same trigger contruct and under each "IF" I checked to see whether the columns old and new values have changed and so on and so forth....SO I thought I could do that same thing SQLServer.. Now IF UPDATE(col_name) will not solve my problem since I change the text depending on INSERT ( I input this value "a" has been inserted) and for UPDATE(I input this value has been modifed from value a to value b) and DELETE(I input the value b has been deleted) in the history description columnAny other ideasThanks |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-16 : 01:23:52
|
Try this:IF (EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED))BEGIN PRINT 'This is an UPDATE'ENDELSEBEGIN IF EXISTS(SELECT * FROM INSERTED) BEGIN PRINT 'This is an INSERT' END ELSE BEGIN PRINT 'This is a DELETE' ENDEND Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2003-09-16 : 12:30:15
|
| Thanks a ton warrior!! That is exactly what I wanted. |
|
|
|
|
|
|
Help on Trigger code