How to create an SQL trigger

Create a table to use as a dataset:

create table triggerTableData(numberVal int(30), charVal char(30), secondNumberVal int(30));

Next, lets populate this table with some values.

insert into triggerTableData values(1, "a", 2), (3, "b", 3), (2,"c",8), (3,"c",9);

Triggers are created within the namespace of databases, but are associated with tables. The triggers can be associated with different update operations, such as insert, update, or delete. The trigger will also occur either before or after the update has occurred. Let’s create a statement to specify this:

delimiter //
create trigger incrementTrig_bu before update on triggerTableData for each row

begin
if NEW.numberVal = 6 then
set NEW.charVal = 'Updated, and value is 6';
end if;
end;
//
delimiter ;

This is a before update trigger. It is ‘triggered’ before any update statement affects the ‘triggerTableData table.

The first line indicates that the delimiter is set to ‘//‘. This can be set to any value, but is necessary as the semicolons within the trigger would otherwise terminate the creation of the trigger prematurely. It is a useful convention to name triggers ending in an underscore, followed by two letters. It may be followed by either a ‘b‘ or ‘a‘ for before or after, and the first letter of the type of statement that causes the trigger. This is a common suffix, but triggers have no required naming scheme.

In this trigger, the begin/end block starts (which can always be nested). A conditional statement then occurs. Column values existing before the triggering statement (statement that caused the trigger) occurs can be prefixed by OLD. if they are to be referenced. NEW. as a prefix will reference the result after the statement occurs. NEW can be used for insert triggers and update triggers only. OLD can be used for delete triggers and update triggers only.

The result of the update is examined, and the charVal column is set if the examined condition is met. The conditional statement is terminated, as is the begin/end block, both of which have trailing semicolons. The if and begin lines do not. The delimiter is then reset to ‘;‘ after the trigger creation statement is completed by the ‘//‘.

Next, lets update the values, then see the results:

update triggertabledata set numberVal = numberVal * 2;

select * from triggertabledata;

You should see that the charVal column is updated only when the numberVal column contains the value 6.