I’ll use the example off the MySQL website (explained below in detail). I’ll assume you know how to create a table. If not, please read up on that first.
CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT); CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 );
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; |
DELIMITER ;
First, we need to change the end of line delimiter. We do this because the statements in the trigger need to end in a semicolon, this is explained further below. This is simple:
DELIMITER |
Next, we specify the action and table for this trigger. In this example, the table is “test1″, and the trigger will execute before any inserts.
CREATE TRIGGER testref BEFORE INSERT ON test1
We specify the following line to make sure if we’re doing a batch update it’ll apply the trigger to each row inserted.
FOR EACH ROW BEGIN
Now we can specify the actions that will occur as the trigger. We use the keywords NEW and OLD to reference the columns in the row. On an INSERT, there are no OLD columns.
INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
We’re done - end the trigger. To finish this single statement (because this entire trigger is really 1 statement, remember we changed our end of statement delimiter) we end it with the |
END; |
We can now change back the end of line delimiter to a semi-colon.
DELIMITER ;
You will now have a trigger on the table ‘test1′ that occurs on every insert.
A few notes: As of MySQL 5, triggers on DELETES will not run if you delete via cascading foreign keys. On an INSERT, you can only use NEW for column values. On a DELETE, you must use OLD.
As per viewers request : Support for triggers is included beginning with MySQL 5.0.2. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.
Some uses for triggers are : a) to perform checks of values to be inserted into a table b) to perform calculations on values involved in an update.
A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement.
NOTE:- A DROP DATABASE on a database with triggers fails, so you must drop triggers before droping database. A DROP TRIGGER fails if the table doesn't exists, so drop tiggers before.
There are some limitations on what can appear in statements that a trigger executes when activated:
The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are allowed to return data to the trigger through OUT or INOUT parameters.)
The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK.
Prior to MySQL 5.0.10, triggers cannot contain direct references to tables by name.
MySQL handles errors during trigger execution as follows:
If a BEFORE trigger fails, the operation on the corresponding row is not performed.
A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.
An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully.
An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.
For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For non-transactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.
Hello Gauri..
ReplyDeleteHere Trigger goes on..
I’ll use the example off the MySQL website (explained below in detail). I’ll assume you know how to create a table. If not, please read up on that first.
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|
DELIMITER ;
First, we need to change the end of line delimiter. We do this because the statements in the trigger need to end in a semicolon, this is explained further below. This is simple:
DELIMITER |
Next, we specify the action and table for this trigger. In this example, the table is “test1″, and the trigger will execute before any inserts.
CREATE TRIGGER testref BEFORE INSERT ON test1
We specify the following line to make sure if we’re doing a batch update it’ll apply the trigger to each row inserted.
FOR EACH ROW BEGIN
Now we can specify the actions that will occur as the trigger. We use the keywords NEW and OLD to reference the columns in the row. On an INSERT, there are no OLD columns.
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
We’re done - end the trigger. To finish this single statement (because this entire trigger is really 1 statement, remember we changed our end of statement delimiter) we end it with the |
END;
|
We can now change back the end of line delimiter to a semi-colon.
DELIMITER ;
You will now have a trigger on the table ‘test1′ that occurs on every insert.
A few notes:
As of MySQL 5, triggers on DELETES will not run if you delete via cascading foreign keys.
On an INSERT, you can only use NEW for column values. On a DELETE, you must use OLD.
As per viewers request :
ReplyDeleteSupport for triggers is included beginning with MySQL 5.0.2.
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.
Some uses for triggers are :
a) to perform checks of values to be inserted into a table
b) to perform calculations on values involved in an update.
A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement.
NOTE:-
A DROP DATABASE on a database with triggers fails, so you must drop triggers before droping database.
A DROP TRIGGER fails if the table doesn't exists, so drop tiggers before.
LIMITATIONS AND MYSQL ERROR HANDLING OF TRIGGER:
ReplyDeleteThere are some limitations on what can appear in statements that a trigger executes when activated:
The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are allowed to return data to the trigger through OUT or INOUT parameters.)
The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK.
Prior to MySQL 5.0.10, triggers cannot contain direct references to tables by name.
MySQL handles errors during trigger execution as follows:
If a BEFORE trigger fails, the operation on the corresponding row is not performed.
A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.
An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully.
An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.
For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For non-transactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.