The triggers here... pretty simple stuff

triggers_sql.md

Here, I will talk about the triggers. As I have learnt triggers way back when I was doing my graduation, it seem like a boring or more complex stuff... but now as I can understand it, it is pretty pretty simple and easy stuff to implement and to understand.

What it is?

It is a way to automatically trigger a query (run a query) when certain event has happened. Like, if I am adding a new user, then automatically new table should be created, or let's say, another entry should be made in another table.

The general syntax

DELIMITER $$

CREATE TRIGGER trigger_name
time_when what_command ON which_table FOR EACH ROW
BEGIN
...
END;

$$

DELIMITER ;

Pretty easy. Now let's see what will come in the individual components:

• time_when: BEFORE / AFTER
• what_command: INSERT / UPDATE / DELETE
• which_table: table_name

Example in simple english: Do this, before inserting in users table.

Example

We want to make restrictions on the table, if the new insert value has age less than 18, then we will reject that insertion.

So,

1. This is check BEFORE insertion.
2. This is done if the INSERTION has happened.
3. And is on the users table.

We have all the pices, let's put them together.

DELIMITER $$

CREATE OR REPLACE IF EXISTS TRIGGER check_age_trigger
BEFORE INSERT ON users FOR EACH ROW
BEGIN
    IF NEW.age < 18
    THEN
        SIGNAL SQLSTATE '25000'
	    SET MESSAGE_TEXT = 'The user must be an Adult!';
    END IF;
END;

$$

NOTE: If the incorrect value is already in there before creating the trigger, the trigger will not show any error, well it is basic right? The trigger will only be "triggered" when the even happens, so the before things will not be considered here.

NOTE: The syntax above is just for MySQL. For PostgreSQL it will have different one.

Checking the pieces:

• NEW.age: "New" is a placeholder. Refers to the new row coming in. And the .age is the column name. (There will be another one called OLD which refers to the deleted row. Which comes in handy when we delete some row. Here we are inserting so NEW.)
• SQLSTATE: Is the error code recognized universally for all SQL (need to look for the docs) Generally use 45000 as general error code.
• MESSAGE_TEXT: What message to show. It should be just MESSAGE_TEXT written in this way only.
• DELIMITER $$: It defines that the end of the query doesn't end with ; but with the $$. So don't think that in the trigger syntax where you see ; means end of the query. It is just the temproary change of delimiter instead of ;.
  • So change the DELIMITER ; after the trigger query.

Another example

Here, we will use DELETE and AFTER. The thing is, we have our user accounts. Now the user deregisters. So we are gonna delete his account from our database... right? But we also want to track those deregistered users. Hence, imeediately after deleting the user from the main table, we will insert his entry in the another deregistered table.

So, the trigger would look like:

DELIMITER $$

CREATE TRIGGER deregister_after_effects_trigger
AFTER DELETE ON users FOR EACH ROW
BEGIN
    INSERT INTO deregistered
    VALUES (OLD.user_id, OLD.dep_id, TIMESTAMP)
END;

$$
DELIMITER ;

And... we are done! Immediately after deleting the row will be in the OLD variable and then there is just a simple insert!