Firing Order Of Triggers

Firing Order of Triggers

The keyword POSITION allows an optional execution order (“firing order”) to be specified for a series of triggers that have the same phase and event as their target.The default position is 0.If no positions are specified, or if several triggers have a single position number, the triggers will be executed in the alphabetical order of their names.

Examples of `CREATE TRIGGER` for Tables and Views

Creating a trigger in the “legacy” form, firing before the event of inserting a new record into the CUSTOMER table occurs.

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.CUST_NO IS NULL) THEN
    NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
END

Creating a trigger firing before the event of inserting a new record into the CUSTOMER table in the SQL standard-compliant form.

CREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END

Creating a trigger that will file after either inserting, updating or deleting a record in the CUSTOMER table.

CREATE TRIGGER TR_CUST_LOG
ACTIVE AFTER INSERT OR UPDATE OR DELETE
ON CUSTOMER POSITION 10
AS
BEGIN
  INSERT INTO CHANGE_LOG (LOG_ID,
                          ID_TABLE,
                          TABLE_NAME,
                          MUTATION)
  VALUES (NEXT VALUE FOR SEQ_CHANGE_LOG,
          OLD.CUST_NO,
          'CUSTOMER',
          CASE
            WHEN INSERTING THEN 'INSERT'
            WHEN UPDATING  THEN 'UPDATE'
            WHEN DELETING  THEN 'DELETE'
          END);
END

With DEFINER set for trigger tr_ins, user US needs only the INSERT privilege on tr.If it were set for INVOKER, either the user or the trigger would also need the INSERT privilege on table t.

create table tr (i integer);
create table t (i integer);
set term ^;
create trigger tr_ins for tr after insert SQL SECURITY DEFINER
as
begin
  insert into t values (NEW.i);
end^
set term ;^
grant insert on table tr to user us;

commit;

connect 'localhost:/tmp/29.fdb' user us password 'pas';
insert into tr values(2);

The result would be the same if SQL SECURITY DEFINER were specified for table TR:

create table tr (i integer) SQL SECURITY DEFINER;
create table t (i integer);
set term ^;
create trigger tr_ins for tr after insert
as
begin
  insert into t values (NEW.i);
end^
set term ;^
grant insert on table tr to user us;

commit;

connect 'localhost:/tmp/29.fdb' user us password 'pas';
insert into tr values(2);

Firing Order Of Triggers

Firing Order of Triggers

Examples of CREATE TRIGGER for Tables and Views

Examples of `CREATE TRIGGER` for Tables and Views