Trigger for DELETE

Jose Saiz

9y892SQL

Hi Guys

I am looking for sample Trigger when deleting a row

For Instance (MS-SQL 2008/2012)

I have a shopping cart that has a customer table with Indentity Column on CustomerId datatype integer, Order table etc.

I would like that if any tech support people or my self that query the table customer via the Query Analyzer and by mistake use the statement DELETE or TRUNCATE

that the trigger either Rolls back the transaction or do not accept the deletion.

The reason for the trigger is because I've noticed that I have lots old orders that became orpham because the customer was deleted, and it is too late to implement the foreign key which helps in this case because it does not allow you to delete the customer that is tied to another table with rows.

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jose Saiz9y ago
Thanks Stasiya
But I am looging not to have a audit trail log table, what I really need is that
when the delete statement is executed that row does not even get deleted to be instact.

Thanks
Nikunj Satasiya9y ago
You Should Try This
CREATE TRIGGER [dbo].[Customer_DELETE]
       ON [dbo].[Customers]
AFTER DELETE
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @CustomerId INT

       SELECT @CustomerId = DELETED.CustomerId
       FROM DELETED

       INSERT INTO CustomerLogs
       VALUES(@CustomerId, 'Deleted')
END