How to create triggers with Syntax

A trigger is set of action programme in mysql and database object which are run automatically when a specified change operation like (INSERT,UPDATE, DELETE) and it can be called before or after event. database support stored procedures and do not require any additional runtime-environment packages for business rules, validating input data, and keeping an audit trail.

What are Triggers?

Triggers are database objects that respond to specific events occurring in a database. These events can be INSERT, UPDATE, or DELETE operations on specific tables. Triggers are designed to execute predefined actions or tasks automatically when the triggering event occurs.

Syntax of the CREATE TRIGGER statement:

CREATE TRIGGER trigger_name    
    --what to do here

trigger_name: Name given to the trigger.

  • BEFORE | AFTER: Specifies whether the trigger fires before or after the triggering event.
  • INSERT | UPDATE | DELETE: Defines the operation that activates the trigger.
  • table_name: The table where the trigger is being applied.
  • FOR EACH ROW: Indicates that the trigger will execute once for each affected row.

Trigger Actions:

Inside the BEGIN and END block, define the actions to be performed when the trigger is activated. This can include SQL statements or procedures based on your database requirements.

Accessing Columns in a Trigger:

You can reference columns from the affected row using the NEW and OLD keywords (specific to different DBMS).

  • NEW.column_name: Represents the new value of the column in an UPDATE or INSERT operation.
  • OLD.column_name: Represents the old value of the column in an UPDATE or DELETE operation.

We can use CREATE TRIGGER keywords

1- The trigger name must be unique within a database.

2- BEFORE or AFTER which indicates that the trigger is invoked before or after.

3- The trigger belongs after the ON keyword.

4- If you want to execute multiple statements, you use the BEGIN END compound statement.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Check Also
Back to top button