Triggers

Description:

Database triggers are database objects that are uniquely named, bound to a specific table and stored within the MySQL system catalog files. MySQL automatically activates a database trigger when any Data Write operation occurs on the table to which database triggers are a bound.

Database triggers, offer developers and Database administrators a way of applying a security layer to table data which is greater than that offered by the MySQL Db engine alone.

Trigger Features

Triggers give developers and administrators, the ability to apply logging / auditing / validation processes to data prior or subsequent to the data being stored in a MySQL table.

Triggers can be uses for:

• Examining data before it is inserted or updated, or verify deletes or updates
• Acting as a data filter by modifying data if it is out of range, before an insert or update
• Modifying how INSERT, UPDATE, and DELETE behave for a table
• Mimicking the behavior of foreign keys for storage engines that do not support foreign keys
• Adding data Logging to table data, prior an Update or Delete operation

And a ton more.

Creating Triggers:

The CREATE TRIGGER statement which has the following syntax, is used to create a database trigger

Syntax:
CREATE TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
triggered_statement

Explanation:
trigger_name is the name given to the trigger, the table_name is the table to which the trigger is specifically bound, BEFORE or AFTER indicates whether the MySQL DB engine will execute the trigger before or after the triggering event (i.e. a table write operation), and INSERT, UPDATE, OR DELETE indicates what the triggering event actually is.

To demonstrate how MySQL triggers work, let's set up two simple tables namely “studentinfo” that has data in it and "studentinfo_audit" that tracks write operations performed on “studentinfo”. Both tables exist within the database “studentdb”.

Table Structure:

Consider the following Database and Table Structure:

Database name: studentdb

Table name: studentinfo

Fields	Datatype	Constraint
id	int	not null
name	varchar(255)	not null
gender	varchar(255)	not null
age	int	not null
major	varchar(255)	not null

Table name: studentinfo_audit

Fields	Datatype	Constraint
id	int	not null
name	varchar(255)	not null
deleted_date	date	not null
deleted_time	time	not null
deleted_by	varchar(255)	not null

Triggers, are able to execute procedural code that modifies data in tables. In this case, we want our trigger to fire after any DELETE statement that executes on the studentinfo table. When a record is deleted in the studentinfo table, the information must be inserted in the studentinfo_audit table.

The following code can be typed in your favorite text editor and pasted into your console at the MySQL prompt. Before you do that though, you want to execute this line:

Before executing the trigger statement, the following should be executed at the command prompt.

mysql> Delimiter $$

NOTE: Our procedural code uses semicolons at the end of statements, so we need to set a different delimiter to let MySQL know when our code block is over, and so that it doesn't stop processing our block when it hits a semicolon.

When you finish your block, you will have to set the delimiter back to the semicolon, or end any subsequent commands with the new delimiter. For example if you made errors in your CREATE TRIGGER block and want to delete it, DROP TRIGGER; won't work unless you set the delimiter back to the semicolon.

You have to write the logic between the BEGIN and END block of trigger.

The following example creates a trigger named studentinfo_AD that executes whenever a delete operation is carried on the studentinfo table ( from the database - studentdb).

mysql>DELIMITER $$
CREATE TRIGGER studentinfo_AD
AFTER DELETE ON studentinfo
FOR EACH ROW
BEGIN
DECLARE v_username varchar(255);
Select user() into v_username
from dual;
INSERT INTO studentinfo_audit(id,name, deleted_date, deleted_time, deleted_by) VALUES
(old.id, old.name, curdate(), curtime(), v_username);
END $$

NOTE: MySQL gives you the OLD and NEW keywords to help you distinguish between old data that may be present in your table row and new data that must over write this old data.

Triggers Events:

Triggers are associated with the tables. The method for activating a trigger is called an “event”. The following list describes the events available:

• BEFORE - these types of events are based on activation times that take place before changes to the data in the table are written to the underlying database. These types of events can capture improper data entries and correct them or reject them prior to storing them. There are three activation events associated with the BEFORE activation time:
  • BEFORE INSERT - this event is triggered prior to new data being added to the table.
  • BEFORE UPDATE - this event is triggered prior to existing data being updated
    (or overwritten) with new data.
  • BEFORE DELETE - this event is triggered prior to data being deleted from the table.

• AFTER – these types of events are based on activation times that take place after changes to the data in the table are written to the underlying database. These types of events can be used for logging or auditing the modification of data within database tables. There are three activation events associated with the AFTER time:
  • AFTER INSERT - this event is triggered after new data was added to the table.
  • AFTER UPDATE - this event is triggered after existing data has been updated (or overwritten) with new data.
  • AFTER DELETE - this event is triggered after data has been deleted from the tables.

• Foreign keys with cascading behavior currently do NOT activate triggers.
  

The procedure to create and use the studentinfo_AD trigger is as follows:

1. First, check to see if a trigger of this name/type already exists. In this case, there is no trigger as shown in Diagram 1.

mysql> SHOW TRIGGERS \G;

Diagram 1

2. Create the table studentinfo_audit as shown in Diagram 2, where on deletion of any data in the studentinfo table the data deleted should be stored in the studentinfo_audit table.

mysql> Create table studentinfo_audit(id INT NOT NULL, name VARCHAR(255) NOT NULL, deleted_date date NOT NULL, deleted_time time NOT NULL, deleted_by VARCHAR(255) NOT NULL);

Diagram 2

3. Create a trigger, named studentinfo_AD. The suffix AD is used to indicate that the trigger will fire AFTER A DELETE as shown in Diagram 3. The FOR EACH ROW in the syntax means that this trigger executes once “for each row deleted”.

mysql> DELIMITER $$
CREATE TRIGGER studentinfo_AD
AFTER DELETE ON studentinfo
FOR EACH ROW
BEGIN
DECLARE v_username varchar(255);
Select user() into v_username
from dual;
INSERT INTO studentinfo_audit(id, name, deleted_date, deleted_time, deleted_by) VALUES
(old.id, old.name, curdate(), curtime(), v_username);
END $$

mysql> DELIMITER ;

Diagram 3

4. Confirm that the studentinfo_AD trigger now exists as shown in Diagram 4.

mysql>SHOW TRIGGERS \G;

Diagram 4

5. Perform a query to confirm the existence of studentinfo data that fits the trigger criteria, before deleting.

mysql>SELECT * FROM studentinfo;

Diagram 5

6. Perform a DELETE on the studentinfo table that will cause the delete data to be placed in the studentinfo_audit table.

mysql>DELETE FROM studentinfo WHERE Name=’Prerna’;

Diagram 6

7. Perform another query to confirm the deletion of the same name data. You will notice that the name of ‘prerna’ has been removed from the studentinfo table.

mysql> SELECT * FROM studentinfo WHERE name=’ prerna’;

Diagram 7

8. Perform a query on the studentinfo_audit trigger table. Notice that the deleted data has been inserted into the studentinfo_audit table.

mysql>SELECT * FROM studentinfo_audit;

Diagram 8

The After Delete trigger has executed successfully.

Trigger Error Handling:

Errors during trigger execution are handled as follows:

• If a BEFORE trigger event fails, the operation on the corresponding row is not performed.
• An AFTER trigger event is executed only if the BEFORE trigger event and the row operation both execute successfully.
• For transactional tables, failure of a trigger should cause a rollback of all changes performed by the statement. For non-transactional tables, such a rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.

Drop Triggers:

Syntax:
DROP TRIGGER trigger_name;

mysql> Drop trigger studentinfo_AD;

Diagram 9

This statement drops a trigger. However, when a table is deleted, all the triggers associated with that table are also deleted.

When using DROP TRIGGER trigger_name, the server will look for that trigger name in the current schema. If the trigger_name that is being deleted is in another schema, the issuer of the statement can include the schema name:

Syntax:
DROP TRIGGER schema_name.trigger_name;

NOTE: if you drop a table, the triggers are automatically dropped. There is no IF EXISTS option available with DROP TRIGGER.

Restrictions on Triggers:

Triggers bring a level of power and security down to the individual record sets of data within a table. However, there are limitations to triggers. The following are not allowed:

• SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE).
• Statements that do explicit or implicit COMMIT or ROLLBACK.
• Statements that return a result set. This includes SELECT statements that do not have an INTO var_list clause and SHOW statements. A trigger can process a result set either with SELECT .. INTO var_list or by using a cursor and FETCH statements.
• FLUSH statements.
• Recursive statements. That is, triggers cannot be used recursively.

Advantages of using SQL triggers:

• An SQL Trigger provides an alternative way to check integrity.
• An SQL trigger can catch the errors in business logic in the database level.
• An SQL trigger provides an alternative way to run scheduled tasks. With SQL trigger, you don’t have to wait to run the scheduled tasks. You can handle those tasks before or after changes being made to database tables.
• An SQL trigger is very useful when you use it to audit the changes of data in a database table.

• < Prev