MySQL Delete Trigger

I am trying to write an "AFTER DELETE" trigger.

The purpose of the trigger is when a record is deleted it is placed into an archive table

CREATE DEFINER=`root`@`%` TRIGGER trg_ID
    AFTER DELETE ON tbl
    FOR EACH ROW  
	BEGIN

		INSERT INTO deleted_id (ID, Title, DDAI_TitleID)
 
	VALUES
			(ID = OLD.ID,
			Title = OLD.Title,
			DDAI_TitleID = OLD.DDAI_TitleID)
	END

Open in new window


From searching on-line I am expecting this to work.

Any Ideas
BrogrimInformation Systems Development ManagerAsked:
Who is Participating?
 
Chris StanyonConnect With a Mentor Commented:
Try this:

DELIMITER //
CREATE DEFINER=`root`@`%` TRIGGER trg_ID
    AFTER DELETE ON tbl
    FOR EACH ROW  
	BEGIN
		INSERT INTO deleted_id (ID, Title, DDAI_TitleID)
	VALUES
		(OLD.ID, OLD.Title, OLD.DDAI_TitleID);
	END;
//

Open in new window

0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
I ran the above an tested it but it put all null values into each column for all records
0
 
Chris StanyonCommented:
I've tested it on my own server and it runs fine. Obviously I can't test against your data, but it should run OK.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
BrogrimInformation Systems Development ManagerAuthor Commented:
It may be something wrong with the table, each default value is set to Null.

I have tried to change it but the Null remains.
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
Thanks
0
 
Chris StanyonCommented:
The default values shouldn't make a difference, as you're explicitly setting the column values. As I say, I can't test it against your data or server, but local testing works as expected.
0
All Courses

From novice to tech pro — start learning today.