0
CREATE TRIGGER trgEmp ON Emp
AFTER DELETE

AS
BEGIN

IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        INSERT INTO EmpAudit
        (   
            Change,
            Date,
            empID, 
            fName, 
            lName, 
            ssn, 
            address,
            state, 
            zip, 
            serialNoJob,
            dob, 
            classification, 
            pay
        )
 SELECT 'DELETED', GETDATE(), d.empID, d.fName, d.lName, d.ssn, d.address, d.state, d.zip, d.serialNoJob,
        d.dob, d.classification, d.pay
        FROM Emp e inner join deleted d on e.empID = d.empID
    END

END
GO

This trigger also had AFTER UPDATE, INSERT. The updates and inserts work as expected. So I decided to try the trigger with delete by itself. I tested again by deleting from Emp table, I still get 0 rows affected 1 rows affected and nothing is actually inserted into the EmpAudit Table

2

After delete trigger is executed after the record has been removed from the table.

Joining a table Emp will yield no result because record with that ID does not exist in that table.

Also note that inserted table will always be empty in after delete trigger.

  • Oh ok that makes sense. Then the best trigger to use then is ON Delete – Bido Jul 13 '17 at 11:12
  • use INSTEAD OF DELETE, and after you audit the record, delete it – S4V1N Jul 13 '17 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.