Complete example of Triggers in insert,Update and delete scenario


/ Published in: SQL
Save to your folder(s)



Copy this code and paste it in your HTML
  1. 2> SELECT * FROM employee
  2. 3> GO
  3. ID name salary start_date city region
  4. ----------- ---------- ----------- ----------------------- ---------- ------
  5. 1 Jason 40420 1994-02-01 00:00:00.000 NEW York W
  6. 2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
  7. 3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
  8. 4 Linda 40620 1997-11-04 00:00:00.000 NEW York N
  9. 5 David 80026 1998-10-05 00:00:00.000 Vancouver W
  10. 6 James 70060 1999-09-06 00:00:00.000 Toronto N
  11. 7 Alison 90620 2000-08-07 00:00:00.000 NEW York W
  12. 8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
  13. 9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
  14.  
  15. (9 ROWS affected)
  16. 1>
  17. 2> CREATE TABLE myArchive (
  18. 3> AID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  19. 4> TYPE nvarchar(6) NOT NULL,
  20. 5> whenchanged smalldatetime NOT NULL DEFAULT Getdate(),
  21. 6> ID INT,
  22. 7> newName nvarchar(30),
  23. 8> oldName nvarchar(50)
  24. 9> )
  25. 10> GO
  26. 1>
  27. 2>
  28. 3> CREATE TRIGGER myTriggerINSERT
  29. 4> ON Employee
  30. 5> FOR INSERT
  31. 6> AS
  32. 7> DECLARE @ID INT, @Name nvarchar(30)
  33. 8>
  34. 9> SET @ID = (SELECT ID FROM inserted)
  35. 10> SET @Name = (SELECT Name FROM inserted)
  36. 11>
  37. 12> INSERT myArchive (TYPE, ID, newName) VALUES('INSERT', @ID, @Name)
  38. 13> GO
  39. 1>
  40. 2>
  41. 3> CREATE TRIGGER myTriggerDELETE
  42. 4> ON Employee
  43. 5> FOR DELETE
  44. 6> AS
  45. 7> DECLARE @ID INT, @Name nvarchar(30)
  46. 8>
  47. 9> SET @ID = (SELECT ID FROM deleted)
  48. 10> SET @Name = (SELECT Name FROM deleted)
  49. 11>
  50. 12> INSERT myArchive (TYPE, ID, oldName ) VALUES('DELETE', @ID, @Name)
  51. 13> GO
  52. 1>
  53. 2> CREATE TRIGGER myTriggerUPDATE
  54. 3> ON Employee
  55. 4> INSTEAD OF UPDATE
  56. 5> AS
  57. 6>
  58. 7> DECLARE @ID INT, @newName nvarchar(30), @oldName nvarchar(30)
  59. 8>
  60. 9> IF (SELECT ID FROM inserted) <> (SELECT ID FROM deleted)
  61. 10> RAISERROR ('You are not allowed to change ID.', 10,1)
  62. 11> ELSE
  63. 12> BEGIN
  64. 13>
  65. 14> --set local variables
  66. 15> SET @ID = (SELECT ID FROM inserted)
  67. 16> SET @newName = (SELECT Name FROM inserted)
  68. 17> SET @oldName = (SELECT Name FROM deleted)
  69. 18>
  70. 19> --write to table
  71. 20> UPDATE Employee SET Name = @newName WHERE ID = @ID
  72. 21> -- write to archive
  73. 22> INSERT myArchive (TYPE, ID, newName, oldName) VALUES('UPDATE', @ID, @newName, @oldName)
  74. 23> END
  75. 24> GO
  76. 1>
  77. 2> INSERT Employee (id, name) VALUES (13, 'Rickie')
  78. 3> GO
  79.  
  80. (1 ROWS affected)
  81. 1>
  82. 2> SELECT * FROM myArchive
  83. 3> GO
  84. AID TYPE whenchanged ID newName oldName
  85. ----------- ------ -------------------- ----------- ------------------------------ ------------------
  86. 1 INSERT 2006-10-10 20:21:00 13 Rickie NULL
  87.  
  88. (1 ROWS affected)
  89. 1>
  90. 2> UPDATE Employee
  91. 3> SET Name = 'Rick'
  92. 4> WHERE ID = 3
  93. 5> GO
  94.  
  95. (1 ROWS affected)
  96.  
  97. (1 ROWS affected)
  98. 1>
  99. 2> SELECT * FROM myArchive
  100. 3> GO
  101. AID TYPE whenchanged ID newName oldName
  102. ----------- ------ -------------------- ----------- ------------------------------ ------------------
  103. 1 INSERT 2006-10-10 20:21:00 13 Rickie NULL
  104. 2 UPDATE 2006-10-10 20:21:00 3 Rick Celia

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.