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
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 ;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
Format a Number with Commas But Without Decimals in SQL Server 2008 R2
Cascade on Delete or Use Triggers
Creating Nondeterministic Functions in SQL Server Using Rand()
Pivot - SQL - Values from Subquery
Does the Number of Columns Returned Affect the Speed of a Query
What Is the Resource Impact from Normalizing a Database
Sqlite: How to Select "Most Recent Record for Each User" from Single Table with Composite Key
Cast VS Ssis Data Flow Implicit Conversion Difference
Calculate Missing Date Ranges and Overlapping Date Ranges Between Two Dates
SQL Query to Select the 'Next' Record (Similar to First or Top N)
Group by Using Parameters in SQL
Why Does My Entity Framework Turn '2.87' into Just '2' (Decimal Field)
Concatenate Multiple Rows in One Field in Access