StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>After a lot of searching I could not find an exact example of a single SQL Server trigger that handles all (3) three conditions of the trigger actions INSERT, UPDATE, and DELETE. I finally found a line of text that talked about the fact that when a DELETE or UPDATE occurs, the common DELETED table will contain a record for these two actions. Based upon that information, I then created a small Action routine which determines why the trigger has been activated. This type of interface is sometimes needed when there is both a common configuration and a specific action to occur on an INSERT vs. UPDATE trigger. In these cases, to create a separate trigger for the UPDATE and the INSERT would become maintenance problem. (i.e. were both triggers updated properly for the necessary common data algorithm fix?)</p> <p>To that end, I would like to give the following multi-trigger event code snippet for handling INSERT, UPDATE, DELETE in one trigger for an Microsoft SQL Server.</p> <pre><code>CREATE TRIGGER [dbo].[INSUPDDEL_MyDataTable] ON [dbo].[MyDataTable] FOR INSERT, UPDATE, DELETE AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with caller queries SELECT statements. -- If an update/insert/delete occurs on the main table, the number of records affected -- should only be based on that table and not what records the triggers may/may not -- select. SET NOCOUNT ON; -- -- Variables Needed for this Trigger -- DECLARE @PACKLIST_ID varchar(15) DECLARE @LINE_NO smallint DECLARE @SHIPPED_QTY decimal(14,4) DECLARE @CUST_ORDER_ID varchar(15) -- -- Determine if this is an INSERT,UPDATE, or DELETE Action -- DECLARE @Action as char(1) DECLARE @Count as int SET @Action = 'I' -- Set Action to 'I'nsert by default. SELECT @Count = COUNT(*) FROM DELETED if @Count > 0 BEGIN SET @Action = 'D' -- Set Action to 'D'eleted. SELECT @Count = COUNT(*) FROM INSERTED IF @Count > 0 SET @Action = 'U' -- Set Action to 'U'pdated. END if @Action = 'D' -- This is a DELETE Record Action -- BEGIN SELECT @PACKLIST_ID =[PACKLIST_ID] ,@LINE_NO = [LINE_NO] FROM DELETED DELETE [dbo].[MyDataTable] WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO END Else BEGIN -- -- Table INSERTED is common to both the INSERT, UPDATE trigger -- SELECT @PACKLIST_ID =[PACKLIST_ID] ,@LINE_NO = [LINE_NO] ,@SHIPPED_QTY =[SHIPPED_QTY] ,@CUST_ORDER_ID = [CUST_ORDER_ID] FROM INSERTED if @Action = 'I' -- This is an Insert Record Action -- BEGIN INSERT INTO [MyChildTable] (([PACKLIST_ID] ,[LINE_NO] ,[STATUS] VALUES (@PACKLIST_ID ,@LINE_NO ,'New Record' ) END else -- This is an Update Record Action -- BEGIN UPDATE [MyChildTable] SET [PACKLIST_ID] = @PACKLIST_ID ,[LINE_NO] = @LINE_NO ,[STATUS]='Update Record' WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO END END </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload