Triggers mysql sql

Question

Whats wrong with this code?

CREATE TRIGGER User_trigger AFTER DELETE ON users
   FOR EACH ROW
BEGIN
   INSERT INTO del_users ('fullname') VALUES ('fullname');
END;

Please help.

Answer 1

There are several issues:

1. If you use BEGIN ... END block you have to change DELIMITER. On the other hand if your trigger contains only one statement just don't use BEGIN ... END. Take a closer look at Defining Stored Programs
2. In MySQL to be able to refer to columns of a row being deleted you have to use OLD keyword. Take a closer look at Trigger Syntax and Examples

That being said and assuming that your simplified table schema look something like this

CREATE TABLE users
(
  id int not null auto_increment primary key,
  fullname varchar(8)
);
CREATE TABLE del_users
(
  id int not null auto_increment primary key, 
  user_id int, fullname varchar(32),
  deleted datetime
);

Your trigger would look

CREATE TRIGGER tg_ad_users 
AFTER DELETE ON users
FOR EACH ROW
  INSERT INTO del_users (user_id, fullname, deleted)
  VALUES (OLD.id, OLD.fullname, NOW());

Here is SQLFiddle demo

or with BEGIN ... END block

DELIMITER //
CREATE TRIGGER tg_ad_users 
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO del_users (user_id, fullname, deleted)
  VALUES (OLD.id, OLD.fullname, NOW());
END//
DELIMITER ;

Here is SQLFiddle demo

Answer 2

Try the following:

CREATE TRIGGER User_trigger AFTER DELETE 
ON users
AS
    INSERT INTO del_users (fullname) 
    SELECT d.fullname from Deleted d

Here is information on using the DELETED and INSERTED Tables:

http://technet.microsoft.com/en-us/library/ms191300.aspx