daily pastebin goal
16%
SHARE
TWEET

Untitled

a guest Dec 7th, 2017 47 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
Top