Such kind of trigger may be applied to views only. It could be used for insert, update and delete operations as usual.
Example:
CREATE TRIGGER trigger1
INSTEAD OF INSERT ON v1
FOR EACH ROW
BEGIN
INSERT INTO t2 VALUES( 1 );
END ;
“trigger1” will be fired on attempt to insert record in the view “v1”.
Starting from v 4.1 you may use ‘NEW’ and ‘OLD’ descriptors within this kind of trigger.
Views are always read-only – so using the trigger is a nature way to overcome this restriction.
Example:
CREATE TABLE t1 (f1 LONG, f2 TEXT(1024));
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TRIGGER trigger1
INSTEAD OF INSERT ON v1
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES( NEW.f1, NEW.f2 );
END ;
—
INSERT INTO v1 VALUES ( 1, ‘abc’);
SELECT * FROM v1;
————-
f1 f2
————-
1 abc
————-