SQL Triggers

An SQL Trigger is an event that sets off a number of other events that happens when a user interacts with a particular table. This particular SQL statement helps by validating form data, or enforcing rules.

A trigger is executed either before or after the following events:

• INSERT – when a new row is inserted.
• UPDATE – when an existing row is updated.
• DELETE – when a row is deleted.

Which trigger goes off and what happens depends on how it is written. Let’s get started by crafting the create statement: 

CREATE TRIGGER <trigger_name> [BEFORE|AFTER] [INSERT|UPDATE|DELETE]
ON <table_name> [FOR EACH ROW|FOR EACH STATEMENT]
BEGIN
  <trigger_logic>
END;

Between any set of <>, replace with your custom names or logic. Inside the [ ], choose one choice from the available given. 

BEFORE/AFTER and INSERT|UPDATE|DELETE: Choose one from each list. BEFORE/AFTER will determine when the trigger happens. INSERT/UPDATE/DELETE is the event that occurs that will start the trigger. 

FOR EACH ROW|FOR EACH STATEMENT: The difference between the two choices here is the difference between how many times the trigger runs. FOR EACH ROW triggers on each row affected in the table. FOR EACH STATEMENT is always guaranteed to run at least once, whether or not any rows are updated.

Similar to databases or tables, there is a DROP TRIGGER option:

DROP TRIGGER [IF EXISTS] trigger_name;

IF EXISTS is an optional portion of the syntax. If included in the statement block, the portion prevents a “trigger doesn’t exist” error. A deleted table automatically drops all associated triggers as well.

Know that SQL trigger requires three main things: a create statement that gives the trigger its name and when the event occurs, how often the trigger occurs, and then a BEGIN/END block that lays out the logic for the actual trigger. 

Keep practicing if you don’t get it at first – because it’s an advanced concept in SQL, it will likely take more repetition than other topics and functions in SQL to cover.