How to Fire a Trigger Before a Delete in T-Sql 2005

How can I fire a trigger BEFORE a delete in T-SQL 2005?

You can use the INSTEAD OF option, just explicitly delete the rows at the end. For example:

CREATE TRIGGER dbo.My_Table_Delete_Instead_Of_Trigger
ON dbo.My_Table
INSTEAD OF DELETE
AS
BEGIN

     -- Do some stuff here

     DELETE T
     FROM DELETED D
     INNER JOIN dbo.My_Table T ON T.PK_1 = D.PK_1 AND T.PK_2 = D.PK_2
END

This assumed a primary key made up of columns PK_1 and PK_2.

How to emulate a BEFORE DELETE trigger in SQL Server 2005

You can use an INSTEAD OF trigger. It fires before (replaces) the actual delete, therefore the linked record in [one_two] must still exist.

create table [one] (one_id int not null primary key)
create table [two] (two_id int not null primary key)
create table [one_two] (one_id int, two_id int references two(two_id) on delete cascade)
GO
CREATE trigger t_del_two
on two
instead of delete
as
begin
SET NOCOUNT ON
DECLARE @Statement NVARCHAR(max)
SET @Statement = ''
SELECT @Statement = @Statement + N'EXEC [MyProc] ''' + CAST([one_two].[one_id] AS VARCHAR(36)) + '''; '
FROM deleted
JOIN [one_two] ON deleted.[two_id] = [one_two].[two_id]

PRINT (@Statement)
--EXEC (@Statement)

-- carry out the actual delete
DELETE TWO WHERE two_id in (SELECT two_id from deleted)
end
GO

Some sample values

insert into one select 1
insert into one select 2
insert into one select 3
insert into two select 11
insert into two select 12
insert into two select 13
insert into one_two select 1,11
insert into one_two select 1,13
insert into one_two select 2,13

Now test it

delete two where two_id=13

Instead of trigger before delete

It fires instead of the delete. If you don't implement it yourself nothing gets deleted. These triggers are usually used on Views not Tables.

CREATE TABLE T(
C INT IDENTITY(1,1) PRIMARY KEY,
D INT)

INSERT INTO T (D)
SELECT 1 UNION ALL SELECT 2

GO

CREATE TRIGGER tr ON T
INSTEAD OF DELETE 
AS
BEGIN 
PRINT 'Do Nothing'
END

GO

DELETE 
FROM T

SELECT * 
FROM T     /*The 2 rows are still there*/

GO         /*Implement the trigger*/

ALTER TRIGGER tr ON T
INSTEAD OF DELETE 
AS
BEGIN 
DELETE T
FROM T 
JOIN DELETED D 
ON T.C = D.C
END

GO

DELETE 
FROM T

SELECT * 
FROM T      /*The 2 rows are now gone*/

DROP TABLE T

SQL Server ON DELETE Trigger

CREATE TRIGGER sampleTrigger
    ON database1.dbo.table1
    FOR DELETE
AS
    DELETE FROM database2.dbo.table2
    WHERE bar = 4 AND ID IN(SELECT deleted.id FROM deleted)
GO

Regarding SQL Server delete trigger

You are basically asking how to pass the parameter into the trigger?

You can use set CONTEXT_INFO inside the procedure and read this inside the trigger.

DECLARE @name VARBINARY(128) 

SET @name = CAST('Martin' AS VARBINARY(128));

SET CONTEXT_INFO @name /*Set it*/

SELECT CAST(CONTEXT_INFO() AS VARCHAR(128)) /*Read it*/

SET CONTEXT_INFO 0x /*Reset it*/

How to create a before delete trigger in SQL Server?

In this situation, you're probably better off doing a regular "after" trigger. This is the most common approach to this type of situation.

Something like

CREATE TRIGGER TRG_AUD_DEL
ON yourTable
FOR DELETE
AS
     INSERT INTO my_audit_table  (col1, col2, ...)
     SELECT col1, col2...
     FROM DELETED 

What will happen is, when a record (or records!) are deleted from your table, the deleted row will be inserted into my_audit_table The DELETED table is a virtual table that contains the record(s) as they were immediately prior to the delete.

Also, note that the trigger runs as part of the implicit transaction on the delete statement, so if your delete fails and rolls back, the trigger will also rollback.

Display Results Before Delete SQL Server

@JoeC - using a proc is probably the best answer.

If you must use a trigger then remmeber that it must be coded to support sets. If someone executes delete order_details where order_id = 10001 then your trigger will need to return the stock level for every product on the order.

Also, when coding a trigger, you have access to a built in table named deleted. This table contains the records deleted.

So you can do something like this:

CREATE TRIGGER deleteOrderTrigger
ON order_details
FOR DELETE
AS

INSERT INTO deleted_order_products_log

SELECT order_details.product_id
      ,products.name
      ,[Quantity being deleted from order] = order_details.quantity
      ,[In Stock Quantity after Deletion] = SUM(products.quantity_in_stock) + order_details.quantity
  FROM order_details
    INNER JOIN deleted d
        ON order_details.primaryKey = d.primaryKey
      LEFT JOIN products
          ON products.product_id = order_details.product_id
 GROUP BY order_details.product_id
         ,products.name
         ,order_details.quantity;

You can then query the log file to get the results of the calculation.

Using AFTER INSERT, UPDATE, DELETE DML trigger. How reference triggering row?

There are no for-each-row triggers in SQL Server. Instead in a trigger you can access the inserted and deleted pseudo tables. inserted contains the rows that get inserted or the changed version of a row for an update and deleted the deleted rows or the version of a row before an update.

So you'd need to do use something like the following to update the sum. It first gets the sum per ID for inserted and analog for deleted then full joins the results -- a full join is needed here since not all IDs are necessarily in both sets -- and then updates main.

UPDATE m
       SET m.total = m.total + z.total
       FROM main m
            INNER JOIN (SELECT coalesce(x.main_fk, y.main_fk) main_fk,
                               coalesce(x.total, 0) - coalesce(y.total, 0) total
                               FROM (SELECT i.main_fk,
                                            sum(i.amount) total
                                            FROM inserted i
                                            GROUP BY i.main_fk) x
                                    FULL JOIN (SELECT d.main_fk,
                                                      sum(d.amount) total
                                                      FROM delete d
                                                      GROUP BY d.main_fk) y
                                              ON y.main_fk = x.main_fk) z
                       ON z.main_fk = m.id;

(Maybe you want to set the total in main to NULL if there aren't any sub records left. The query above doesn't do that, that'd need some extra work.)

But physically storing such figures that can be computed from others bears the risk of inconsistencies. If the trigger is disabled or doesn't work (properly) for other reasons for a period in time, changes to the sub table are not (correctly) reflected in the main table. You'd have false figures in there and possibly wouldn't even recognize it.

I'd avoid such a thing and opt for a view instead if possible. (It's easier to write too. ;))

CREATE VIEW main_with_total
AS
SELECT m.id,
       sum(s.amount) total
       FROM main m
            LEFT JOIN sub s
                      ON s.main_fk = m.id
       GROUP BY m.id;

(Here you'd get NULL as total for an ID where no sub records exist. If you want 0 instead change the expression for total to coalesce(sum(s.amount), 0).)

---

Related Topics