CREATE TRIGGER ON view_name INSTEAD OF INSERT|UPDATE|DELETE AS
FOR EACH ROW
...
Triggers
Views are abstractions above physical sources.
They typically union or join information from multiple tables, often from multiple data sources or other views.
Teiid can perform update operations against views.
Update commands that you run against a view (INSERT
, UPDATE
, or DELETE
) require logic to define how the tables and views
integrated by the view are affected by each type of command.
This transformation logic, also referred to as a trigger, is invoked when an update command is issued against a view.
Update procedures define the logic for how the update command that you run against a view is decomposed into
the individual commands to be executed against the underlying physical sources.
Similar to virtual procedures, update procedures have the ability to execute queries or other commands,
define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.
For more inmformation about virtual procedures, see Virtual procedures.
You can use INSTEAD OF
triggers on views in a way that is similar to the way that you might use them with traditional databases.
You can have only one FOR EACH ROW
procedure for each INSERT
, UPDATE
, or DELETE
operation against a view.
-
The user application submits the SQL command.
-
The command detects the view that it is executed against.
-
The correct procedure is chosen depending upon the command type (
INSERT
,UPDATE
, orDELETE
). -
The procedure is executed. The procedure might contain SQL commands of its own. Commands in the procedure can be different in type from the command that is received from the calling application.
-
Commands, as described in the procedure, are issued to the individual physical data sources or other views.
-
A value representing the number of rows changed is returned to the calling application.
Teiid can use AFTER
triggers on source tables.
AFTER
triggers are called by events from a change data capture (CDC) system.
CREATE TRIGGER ON source_table AFTER INSERT|UPDATE|DELETE AS
FOR EACH ROW
...
Only the FOR EACH ROW
construct serves as a trigger handler.
A FOR EACH ROW
trigger procedure will evaluate its block for each row of the view/source affected by the UPDATE
statement.
For UPDATE
and DELETE
statements, this will be every row that passes the WHERE
condition.
For INSERT
statements there will be one new row for each set of values from the VALUES
or query expression.
For a view, the rows updated is reported as this number, regardless of the affect of the underlying procedure logic.
FOR EACH ROW
BEGIN ATOMIC
...
END
The BEGIN
and END
keywords are used to denote block boundaries.
Within the body of the procedure, any valid statement may be used.
Note
|
The use of the ATOMIC keyword is currently optional for backward compatibility,
but unlike a normal block, the default for INSTEAD OF triggers is atomic.
|
You can use a number of special variables when defining your update procedure.
- NEW variables
-
Every attribute in the view/table whose
UPDATE
andINSERT
transformations you are defining has an equivalent variable namedNEW.<column_name>
.When an INSERT or an UPDATE command is executed against the view, or the event is received, these variables are initialized to the values in the
INSERT VALUES
clause or theUPDATE SET
clause respectively.In an
UPDATE
procedure, the default value of these variables, if they are not set by the command, is the old value. In an INSERT procedure, the default value of these variables is the default value of the virtual table attributes. SeeCHANGING
variables, later in this list for distinguishing defaults from passed values. - OLD variables
-
Every attribute on the view/table whose
UPDATE
andDELETE
transformations you are defining has an equivalent variable namedOLD.<column_name>
.When a
DELETE
orUPDATE
command is executed against the view, or the event is received, these variables are initialized to the current values of the row being deleted or updated respectively. - CHANGING variables
-
Every attribute on the view/table whose
UPDATE
andINSERT
transformations you are defining has an equivalent variable namedCHANGING.<column_name>
.When an
INSERT
or anUPDATE
command is executed against the view, or an the event is received, these variables are initialized totrue
orfalse
depending on whether theINPUT
variable was set by the command. ACHANGING
variable is commonly used to differentiate between a default insert value and one that is specified in the user query.For example, for a view with columns A, B, C:
If User Executes… Then… INSERT INTO VT (A, B) VALUES (0, 1)
CHANGING.A = true, CHANGING.B = true, CHANGING.C = false
UPDATE VT SET C = 2
CHANGING.A = false, CHANGING.B = false, CHANGING.C = true
- Key variables
-
To return generated keys from an
INSERT
trigger, a KEY group is made available that can be assigned the value to be returned. Typically this requires using thegenerated_key
system function. However, not all inserts provide generated keys, because not all sources return generated keys.create view v1 (i integer, k integer not null auto_increment primary key) OPTIONS (UPDATABLE true) as select x, y from tbl; create trigger on v1 instead of insert as for each row begin atomic -- ... some logic insert into tbl (x) values (new.i); key.k = cast(generated_key('y') as integer); end;
For example, for a view with columns A, B, C:
FOR EACH ROW
BEGIN
DELETE FROM X WHERE Y = OLD.A;
DELETE FROM Z WHERE Y = OLD.A; // cascade the delete
END
FOR EACH ROW
BEGIN
IF (CHANGING.B)
BEGIN
UPDATE Z SET Y = NEW.B WHERE Y = OLD.B;
END
END
FOR EACH ROW
update procedures in a view can also be used to emulate BEFORE/AFTER
each row triggers while still retaining
the ability to perform an inherent update. This BEFORE/AFTER
trigger behavior with an inherent update can be achieved by
creating an additional updatable view over the target view with update procedures of the form:
CREATE TRIGGER ON outerVW INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
--before row logic
...
--default insert/update/delete against the target view
INSERT INTO VW (c1, c2, c3) VALUES (NEW.c1, NEW.c2, NEW.c3);
--after row logic
...
END