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 |
|
lastnewbie
Starting Member
1 Post |
 Posted - 2006-06-28 : 04:23:30
|
| Hello,I have coded one trigger FOR INSERT, UPDATE, DELETE. I want to know which action (insert, delete or insert) has caused the execution of the trigger. Is there any property that offers that.I do not want to proceed by comparing the rows number nor by compparing the content of the fields.Thank you for your help. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-28 : 04:43:41
|
Is this a Home Work Assignment?DECLARE @Ins INT, @Del INTSELECT @Ins = COUNT(*) FROM insertedSELECT @Del = COUNT(*) FROM deletedIF @Ins > 0 AND @Del > 0 PRINT 'UPDATE action'IF @Ins > 0 AND @Del = 0 PRINT 'INSERT action'IF @Ins = 0 AND @Del > 0 PRINT 'DELETE action' Peter LarssonHelsingborg, Sweden |
 |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-06-28 : 04:47:01
|
| Hi,if you have specified he trigger FOR INSERT, UPDATE, DELETEthen it will be executed whenever an Insert or an update or a delete statement is executed on that table. However, you can check If Update(column) to check whether an statement update statement has fired the trigger execution |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-28 : 05:20:47
|
"If Update(column) to check whether an statement update statement has fired the trigger execution"Inserts will do that too, won't they?If you are trying to build a trigger to copy changed data to an Audit table have look at:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Triggers+to+audit+changesand in particular INSERT dbo.MyAuditTable SELECT [AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END, [AuditDate] = GetDate(), D.* FROM deleted D LEFT OUTER JOIN inserted I ON I.MyPK = D.MyPK Kristen |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
|
|
|
|
|
|
|
Audit trigger