Mega Code Archive
Change NEW value in a before insert trigger
<![CDATA[
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE TRIGGER myTrigger
-> BEFORE INSERT ON employee
-> FOR EACH ROW
-> BEGIN
-> DECLARE row_count INTEGER;
->
-> SELECT COUNT(*)
-> INTO row_count
-> FROM employee
-> WHERE id=NEW.id;
->
-> IF row_count > 0 THEN
-> UPDATE employee
-> SET salary = salary+NEW.salary
-> WHERE id=NEW.id;
-> ELSE
-> INSERT INTO employee (id,salary) VALUES(NEW.id,NEW.salary);
-> END IF;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,'Jason', 'Martin', '19960725', '20060725', 1234.56, 'Toronto', 'Programmer');
ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,'Alison', 'Mathews', '19760321', '19860221', 6661.78, 'Vancouver','Tester');
ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,'James', 'Smith', '19781212', '19900315', 6544.78, 'Vancouver','Tester');
ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,'Celia', 'Rice', '19821024', '19990421', 2344.78, 'Vancouver','Manager');
ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,'Robert', 'Black', '19840115', '19980808', 2334.78, 'Vancouver','Tester');
ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,'Linda', 'Green', '19870730', '19960104', 4322.78,'New York', 'Tester');
ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,'David', 'Larry', '19901231', '19980212', 7897.78,'New York', 'Manager');
ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,'James', 'Cat', '19960917', '20020415', 1232.78,'Vancouver', 'Tester');
ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> select * from Employee;
Empty set (0.00 sec)
mysql>
mysql> drop trigger myTrigger;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>