SQL Triggers for beginners

Trigger is an action that causes something to happen. SQL triggers does the same thing. Technically speaking a trigger is a stored procedure invoked in response to some event such as an insert, update or delete.

Firstly we will look at a very simple example to get an idea of what a trigger does.

Let's say we want to prevent users from entering cuss words as their usernames.

CREATE TABLE users(
 user_name varchar(255),
 age int
);
DELIMITER $$
CREATE TRIGGER prevent_bad_names
  BEFORE INSERT ON users FOR EACH ROW
  BEGIN
      IF NEW.user_name like '%bad%'
      THEN
       SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT='Please dont enter cuss words';
      END IF;
   END;
$$
DELIMITER ;

There might be a few unfamiliar words on the above snippet, but it’s not as complicated as it looks. Let dive into each of the above statements.

Firstly the DELIMITER, it defines a new ending character instead of the usual ‘;’.

Why do we need to do this?

It lets us write complete create trigger in a single go since the trigger also contains the terminating character ‘;’.

As an example if we set DELIMITER as $$ then how we should write a normal select query would be

SELECT * FROM user $$

And finally we reset the DELIMITER to ; so it behaves as usual;

Now we will discuss the create trigger syntax.

CREATE TRIGGER trigger_name 
    trigger_time trigger_event ON table_name FOR EACH ROW
    BEGIN
    ...
    END;

So now we can see that prevent_bad_names was the name given to the trigger. This can be any name you want to give the trigger. This can be useful to drop or remove the trigger.

Next trigger_time there can be only two values which are BEFORE and AFTER. We have used BEFORE since we needed to prevent the insert from happening if there is a bad word in the username. Likewise AFTER means after the actions has already happened.

Now for the trigger_event it can be either an INSERT, DELETE OR UPDATE. We have used INSERT since we want to prevent an insert.

And then we add the table name we want to add the trigger to after the ON keyword.

FOR EACH ROW doesn’t mean that it runs the for each of the rows on the table rather it means that it will run for each of the rows affected by the statement. Let's say we tried inserting one row to the above users table then only that row will be affected and if we insert multiple rows it will run the trigger for each of the rows. This syntax has to be added to in order to create a trigger.

Another confusing word is the NEW keyword used above. We can use either the OLD or NEW keywords here, they are aliases that allows us to access information on a how column was(old) or how it will be(new). So on a insert the old values will basically all be null while the new values are the ones we are inserting. On a update the old values will be the current columns on the record and the new will be the record after the update goes through.

Then finally we can add the actual statements we need inside the begin and end block. It can be a insert statement based on some conditions or maybe a Signal. Signal is basically a way to return an error. SQLSTATE is a code which identifies SQL error conditions. This states are common to all SQL databases. These are predefined states for most of the common errors like no such table found SQLSTATE 42S02.

The value 45000 that I have used above is a generic SQLSTATE which means unhandled user-defined exception which is usually used to return errors on triggers.

Advantages

• Handle errors from the data layer
• Alternate way to run scheduled tasks
• Audit data changes in tables
• Automatically update the updated_at field

Disadvantages

• Difficult to troubleshoot since they are invisible to the client application.
• May increase the overhead of the database since there will be background statements running.

Conclusion

Triggers seem like a solution to some issues we face like

• keeping track of changes that happened in the database
• Enforcing rules
• Deriving additional columns.

But due to the above mentioned obvious disadvantages like troubleshooting problems and increased overheads triggers should only be used when its valid and if it's the most suited solution. But this doesn’t mean triggers are evil they can be very useful as long they are used right.