[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

How to set a After Delete Trigger with SQL Server 2008?

I need to save, in a table, data of the last record deleted.

I am using After DELETE when in the the table one of the columns, which is Booking = 0'


USE [WMS_KRAFT750_PROD]
GO
/****** Object:  Trigger [WMS.Storage].[StockUnitPositionLogger]    Script Date: 10/24/2011 10:17:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Klisch Michal>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================


      CREATE TRIGGER [WMS.Storage].[StockUnitPositionLogger]
            ON  [WMS.Storage].[StockUnitPosition]
       AFTER DELETE
      AS       
      IF EXISTS (SELECT Id FROM [WMS.Storage].[StockUnitPosition]
             where Booking = 0)
      BEGIN
            SET NOCOUNT ON;
            declare @inputbuffer table (EventType nvarchar(30),Parameters int,EventInfo nvarchar(4000))      
            insert into @inputbuffer exec('dbcc inputbuffer('+@@Spid+')')
            insert into LogTable
            select GETDATE(), EventInfo, 1 from @inputbuffer
      END
0
iscivanomar
Asked:
iscivanomar
1 Solution
 
Scott PletcherSenior DBACommented:
CREATE TRIGGER [WMS.Storage].[StockUnitPositionLogger]
            ON  [WMS.Storage].[StockUnitPosition]
       AFTER DELETE
      AS      
      SET NOCOUNT ON;
      IF EXISTS (SELECT Id FROM deleted where Booking = 0)
      BEGIN
            insert into LogTable
            select GETDATE(), ...other columns...
            FROM deleted
            WHERE Booking = 0
      END --IF
 
0
 
iscivanomarAuthor Commented:
Thank you, it work perfectly.

Have a nice day.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now