Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL Delete Trigger

Posted on 2013-11-12
6
Medium Priority
?
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 44

Accepted Solution

by:
Chris Stanyon earned 2000 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 44

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 44

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

688 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