Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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
0
Brogrim
Asked:
Brogrim
  • 3
  • 3
1 Solution
 
Chris StanyonCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now