Category: Valentina SQL

Using of “Instead-of” triggers

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
————-

RAISE now can re-throw exception

Up to now the statement RAISE did have syntax as: 
     raise errNumber [, errMsg]

Now it is 
     raise [errNumber [, errMsg]]

Except syntax also is implemented additional logic:
     raise without parameters means throw the same exception forward.

BEGIN
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(1);
EXCEPTION
WHEN 23503 THEN
PRINT 'Unique violation'
RAISE;
END;