Commented by: @asfernandes

How and for what you would use it?

Commented by: Igor Lobov (ivl)

I have three cases:

1. We could use statement-level triggers for log operations when we need to register just only fact (user name, date and time or/and duration) of operator execution (INSERT, DELETE or UPDATE).
2. We could use it for permissibility checkup of usage such operators for cases with long duration or/and with using UDF (witch check external factors).
3. We could use it for some renumbering operations when we need to use continuous sequences.
We have two tables:
CREATE Table1 (
Id INTEGER NOT NULL,
Field1 <type>,
.....
CONSTRAINT PK_TABLE1 PRIMARY KEY (Id)
);

CREATE Table2 (
Id_Table1 INTEGER NOT NULL,
Continuous_sequence_field INTEGER NOT NULL,
........
CONSTRAINT FK_TABLE2_TABLE1 FOREIGN KEY (Id_Table1) REFERENCES Table1 (Id)
);

In field 'Continuous_sequence_field' I want to have continuous sequence.
If I delete some records from Table1 continuous sequence in Table2 would be corrupted.
I would make renumbering in statement-level trigger (AFTER DELETE).

Commented by: Bernardo Signori (bernardo)

This should be the default behaviour according to the sql standard. The sql standard has an optional clause "for each row" that makes the trigger execute once for each row affected by the statement (the current Firebird behaviour).
Changing the default behaviour of Firebird is obviously not possible but it would be very valuable if this could be added.
Perhaps the optional clause could be [FOR EACH ROW | FOR ALL ROWS]. And add to the documentation that the default is "for each row".
Also the row_count variable could hold the number of affected rows.

Commented by: Ain Valtin (ain)

There is ticket for making create trigger syntax compliant with SQL2003 (CORE711) which is marked as fixed in FB2.1, but unfortunately it only addresses the placement of the table name part of the syntax, not FOR EACH {ROW | STATEMENT} part :(
The current FB behaviour of FOR EACH ROW is sometimes real PITA...