Insert Deleted Values into a Table Before Delete with a Delete Trigger

INSERT deleted values into a table before DELETE with a DELETE TRIGGER

Your problem is: this trigger fires AFTER the delete has already happened. So there is no more row in HashTags which you could join on!

You need to use this trigger instead:

ALTER TRIGGER [dbo].[HashTags_BeforeDelete]
    ON [dbo].[HashTags]
    FOR DELETE
AS
  BEGIN
    INSERT INTO HashTagsArchive(Id, HashTagId, delete_date)
       SELECT 
           d.Id, d.HashTagId, GETUTCDATE() 
       FROM deleted d 
  END
GO

The Deleted pseudo table contains the whole row(s) that were deleted - no need to join on anything...

Also: this trigger fires after the delete has happened - so you don't need to do anything yourself, inside the trigger - just insert those bits of information into your archive table - that's all. Everything else is handled by SQL Server for you.

Delete trigger to insert entry into table

You need something like this - just insert the rows into Customers_Audit in your AFTER DELETE trigger and set the fixed values as needed:

CREATE TRIGGER trgAfterDelete 
ON dbo.Customer
AFTER DELETE
AS 
    -- insert into the audit table - explicitly specifying the column names to use
    INSERT INTO dbo.Customers_Audit(CustomerId, UserName, DeleteDate)
        -- make sure to respect the fact that the "Deleted" table might
        -- contain *multiple* rows if your DELETE statement deleted 
        -- more than a single row - use proper set-based code!
        SELECT
            d.CustomerId, d.UserName, SYSDATETIME()
        FROM 
            Deleted d

Trigger to delete the record from the table if it exists in it

A simple DELETE + JOIN should detect whether the record exists in the future and can then be deleted.

The following delete can be the entire body of your trigger.

    DELETE F
    FROM employee_assignment_future F
    INNER JOIN Inserted I
        ON I.employee_id = F.employee_id 
        AND I.department_id = F.department_id
        AND I.job_title_id = F.job_title_id
        AND I.manager_id = F.manager_id;

Note: You can't assume Inserted (or Deleted) have only a single row. You need to use set based operations and handle it having 0-N rows.

Insert deleted records into history table before DELETE using TRIGGER

Use a FOR DELETE trigger:

CREATE TRIGGER TriggerName    
ON TableName  
FOR DELETE    
AS     
BEGIN    
    INSERT INTO HistoryTable(Col1, Col2, Col3)  
    SELECT Col1, GETDATE(), Col3  
    FROM TableName  
    WHERE DeletedRecordID IN (SELECT DeletedRecordID FROM TableName)     
END  

mysql before delete trigger reinsert record if deleted or updated

1. If you're happy to hard-code into the triggers the details of the record that you wish to preserve, then you could do something like:

   DELIMITER ;;
   
   CREATE TRIGGER foo_upd BEFORE UPDATE ON Users FOR EACH ROW
   IF OLD.Username <=> 'Dames' AND OLD.Password <=> 'Password' THEN
     INSERT INTO Users (Username, Password) VALUES ('Dames', 'Password');
   END IF ;;
   
   CREATE TRIGGER foo_del BEFORE DELETE ON Users FOR EACH ROW
   IF OLD.Username <=> 'Dames' AND OLD.Password <=> 'Password' THEN
     INSERT INTO Users (Username, Password) VALUES ('Dames', 'Password');
   END IF ;;
   
   DELIMITER ;
   

2. Otherwise, I'd recommend adding an additional boolean column to the Users table—e.g. locked:

   ALTER TABLE Users ADD COLUMN locked BOOLEAN NOT NULL DEFAULT FALSE;
   

   Then setting the value of that column to be TRUE on the record(s) that you wish to preserve:

   UPDATE Users SET locked = TRUE
   WHERE  Username = 'Dames' AND Password = 'Password';
   

   Then creating triggers along the following lines:

   DELIMITER ;;
   
   CREATE TRIGGER foo_upd BEFORE UPDATE ON Users FOR EACH ROW
   IF OLD.locked THEN
     INSERT INTO Users (Username, Password) VALUES (OLD.Username, OLD.Password);
   END IF ;;
   
   CREATE TRIGGER foo_del BEFORE DELETE ON Users FOR EACH ROW
   IF OLD.locked THEN
     INSERT INTO Users (Username, Password) VALUES (OLD.Username, OLD.Password);
   END IF ;;
   
   DELIMITER ;
   

Why doesn't the deleted table in a FOR/AFTER DELETE Trigger contain data of the Deleted Record?

You should not be selecting from your Items table since the row(s) you want are now deleted.

Just select from Deleted

Ie

INSERT INTO RecordDeletionTimes
    SELECT
        'dbo',
        'Items',
        ID,
        GETUTCDATE(),
        CONCAT
        (
            'Key: ''',
            [Key],
            ''' Value: ''',
            Value,
            ''''
        )
    FROM deleted ;

See Demo Fiddle

The best way to debug your trigger is to just select * from deleted and test in SSMS using a begin tran/rollback.

---

Related Topics