daily pastebin goal
42%
SHARE
TWEET

Untitled

a guest Dec 7th, 2017 48 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. if exists (select 1
  2.             from  sysobjects
  3.            where  id = object_id('TrigerTable')
  4.             and   type = 'U')
  5. begin
  6.    drop table dbo.TrigerTable;
  7.    drop procedure dbo.CreateTrigerTableRow;
  8. end
  9. GO
  10.  
  11. CREATE TABLE dbo.TrigerTable
  12. (
  13.     ID  int  identity
  14.     , SETTING_VALUE  int
  15. )
  16. GO
  17.  
  18. CREATE PROCEDURE dbo.CreateTrigerTableRow
  19.     @SETTING_VALUE int
  20. AS
  21. BEGIN
  22.     insert into dbo.TrigerTable(SETTING_VALUE) values(@SETTING_VALUE);
  23. END
  24. GO
  25.  
  26. CREATE TRIGGER dbo.TrigerTable_after_Insert_delete
  27.    ON  dbo.TrigerTable
  28.    AFTER INSERT, UPDATE, DELETE
  29. AS
  30. BEGIN
  31.     print N'Start Trigger'
  32.  
  33.     IF EXISTS(select 1 from inserted)
  34.     BEGIN
  35.         IF EXISTS(select 1 from deleted)
  36.         BEGIN
  37.             select 'Table Updated' as EVEN_TYPE
  38.                 , C.SETTING_VALUE as table_VALUE
  39.                 , i.SETTING_VALUE as inserted_VALUE
  40.                 , d.SETTING_VALUE as deleted_VALUE
  41.             from inserted i
  42.             inner join dbo.TrigerTable C
  43.                 on i.ID=C.ID
  44.             inner join deleted d
  45.                 on C.ID=d.ID;
  46.         END
  47.         ELSE
  48.         BEGIN
  49.             select 'Table Insert' as EVEN_TYPE
  50.                 , C.SETTING_VALUE as table_VALUE
  51.                 , i.SETTING_VALUE as inserted_VALUE
  52.                 , d.SETTING_VALUE as deleted_VALUE
  53.             from inserted i
  54.             inner join dbo.TrigerTable C
  55.                 on i.ID=C.ID
  56.             left outer join deleted d
  57.                 on C.ID=d.ID;
  58.         END
  59.     END
  60.     ELSE
  61.     BEGIN
  62.         select 'Table Deleted' as EVEN_TYPE
  63.             , C.SETTING_VALUE as table_VALUE
  64.             , i.SETTING_VALUE as inserted_VALUE
  65.             , d.SETTING_VALUE as deleted_VALUE
  66.         from deleted d
  67.         left outer join inserted i
  68.             on d.ID=i.ID
  69.         left outer join dbo.TrigerTable C
  70.             on i.ID=C.ID;
  71.     END
  72. END
  73. GO
  74.  
  75. /*
  76. TEST: Insert one row
  77. */
  78. insert into dbo.TrigerTable(SETTING_VALUE) values(1);
  79. GO
  80. /*
  81. TEST: Update one row
  82. */
  83. update dbo.TrigerTable
  84. set SETTING_VALUE=2
  85. GO
  86. /*
  87. TEST: Delete one row
  88. */
  89. delete from dbo.TrigerTable
  90. GO
  91. /*
  92. TEST: Delete nothing
  93. */
  94. delete from dbo.TrigerTable
  95. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top