Solved

MySQL Delete Trigger

Posted on 2013-11-12
6
334 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 43

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 43

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 43

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to count in a table in php 22 45
MS SQL Server - Looking to filter rows based on column value 3 48
mysql between clause 2 32
check mysql insert 12 28
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

830 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