Solved

MySQL Delete Trigger

Posted on 2013-11-12
6
326 Views
Last Modified: 2013-11-12
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
Comment
Question by:Brogrim
  • 3
  • 3
6 Comments
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39641259
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
 

Author Comment

by:Brogrim
ID: 39641303
I ran the above an tested it but it put all null values into each column for all records
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39641358
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Brogrim
ID: 39641694
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
 

Author Closing Comment

by:Brogrim
ID: 39641984
Thanks
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39642361
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now