[EMAIL PROTECTED] wrote:
Ticket #2282 against SQLite
http://www.sqlite.org/cvstrac/tktview?tn=2282
complains that in an INSTEAD OF trigger on a view where
the trigger has a WHEN clause, if the WHEN clause is false
and the trigger does not fire, then the UPDATE statement
that provoked the trigger should fail. I am skeptical
of this claim, but want the opinion of others before
I reject the ticket.
Is this really a bug? What do INSTEAD OF triggers with
false WHEN clauses do on other SQL database engines?
Richard,
Instead of triggers are an extension to standard SQL. They do not appear
in the SQL:1999 standard and are only mentioned in passing in the
editors notes for SQL:2003.
They do seem to be a common extension, supported by Oracle, IBM, and
Microsoft in addition to SQLite, but they are all done differently.
PostgreSQL does not support the non standard INSTEAD OF triggers.
From Microsoft's SQL server documentation they don't support a when clause:
CREATE TRIGGER /trigger_name /
ON { /table/ | /view /}
[ WITH ENCRYPTION ]
{
{ {* *FOR | AFTER | INSTEAD OF } { [ INSERT ] [ *, *] [ UPDATE ] [ *, *]
[ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE *( */column /*) *
[ { AND | OR } UPDATE *( */column /*) *]
[ .../n /]
| IF *(* COLUMNS_UPDATED *( ) *{ /bitwise_operator /} /updated_bitmask /*)*
{ /comparison_operator /} /column_bitmask/ [ .../n /]
} ]
/sql_statement/ [* *.../n /]
}
}
INSTEAD OF
Specifies that the trigger is executed /instead of/ the triggering SQL
statement, thus overriding the actions of the triggering statements.
At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement
can be defined on a table or view. However, it is possible to define
views on views where each view has its own INSTEAD OF trigger.
INSTEAD OF triggers are not allowed on updateable views WITH CHECK
OPTION. SQL Server will raise an error if an INSTEAD OF trigger is added
to an updateable view WITH CHECK OPTION specified. The user must remove
that option using ALTER VIEW before defining the INSTEAD OF trigger.
{ [DELETE] [*,*] [INSERT] [*,*] [UPDATE] }
Are keywords that specify which data modification statements, when
attempted against this table*/ /*or view, activate the trigger. At least
one option must be specified. Any combination of these in any order is
allowed in the trigger definition. If more than one option is specified,
separate the options with commas.
For INSTEAD OF triggers, the DELETE option is not allowed on tables that
have a referential relationship specifying a cascade action ON DELETE.
Similarly, the UPDATE option is not allowed on tables that have a
referential relationship specifying a cascade action ON UPDATE.
From mySQL documentation, they don't support a WHEN clause either:
CREATE
[DEFINER = { /|user|/ | CURRENT_USER }]
TRIGGER /|trigger_name|/ /|trigger_time|/ /|trigger_event|/
ON /|tbl_name|/ FOR EACH ROW /|trigger_stmt|/
/|trigger_time|/ is the trigger action time. It can be |BEFORE| or
|AFTER| to indicate that the trigger activates before or after the
statement that activated it.
/|trigger_event|/ indicates the kind of statement that activates the
trigger. The /|trigger_event|/ can be one of the following:
*
|INSERT|: The trigger is activated whenever a new row is inserted
into the table; for example, through |INSERT|, |LOAD DATA|, and
|REPLACE| statements.
*
|UPDATE|: The trigger is activated whenever a row is modified; for
example, through |UPDATE| statements.
*
|DELETE|: The trigger is activated whenever a row is deleted from
the table; for example, through |DELETE| and |REPLACE| statements.
However, |DROP TABLE| and |TRUNCATE| statements on the table do
/not/ activate this trigger, because they do not use |DELETE|. See
Section 13.2.9, “|TRUNCATE| Syntax”
<http://dev.mysql.com/doc/refman/5.0/en/truncate.html>.
It is important to understand that the /|trigger_event|/ does not
represent a literal type of SQL statement that activates the trigger so
much as it represents a type of table operation. For example, an
|INSERT| trigger is activated by not only |INSERT| statements but also
|LOAD DATA| statements because both statements insert rows into a table.
A potentially confusing example of this is the |INSERT INTO ... ON
DUPLICATE KEY UPDATE ...| syntax: a |BEFORE INSERT| trigger will
activate for every row, followed by either an |AFTER INSERT| trigger or
both the |BEFORE UPDATE| and |AFTER UPDATE| triggers, depending on
whether there was a duplicate key for the row.
There cannot be two triggers for a given table that have the same
trigger action time and event. For example, you cannot have two |BEFORE
UPDATE| triggers for a table. But you can have a |BEFORE UPDATE| and a
|BEFORE INSERT| trigger, or a |BEFORE UPDATE| and an |AFTER UPDATE| trigger.
From the IBM DB2 documentation a WHEN clause is not allowed on an
INSTEAD OF trigger (see note 4):
Read syntax diagramSkip visual syntax diagram
<http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000931.htm#skipsyn-200>
.-NO CASCADE-.
-CREATE TRIGGER--/trigger-name/--+-+------------+--BEFORE-+----->
+-AFTER------------------+
'-INSTEAD OF-------------'
--+-INSERT--------------------------+--ON--+-/table-name/-+------>
+-DELETE--------------------------+ '-/view-name/--'
'-UPDATE--+---------------------+-'
| .-,-----------. |
| V | |
'-OF----/column-name/-+-'
--+------------------------------------------------------------------+-->
| .-------------------------------------------------. |
| V (1) (2) .-AS-. | |
'-REFERENCING------------------+-OLD--+----+--/correlation-name/-+-+-'
| .-AS-. |
+-NEW--+----+--/correlation-name/-+
| .-AS-. |
+-OLD TABLE--+----+--/identifier/-+
| .-AS-. |
'-NEW TABLE--+----+--/identifier/-'
--+-FOR EACH ROW--------------+--| triggered-action |---------><
| (3) |
'--------FOR EACH STATEMENT-'
triggered-action:
|--+-------------------------------------+---------------------->
| (4) |
'--------WHEN--(--/search-condition/--)-'
--+--------+--| SQL-procedure-statement |----------------------|
'-/label:/-'
SQL-procedure-statement:
|--+-/CALL/----------------------------------------------+--------|
+-/Compound SQL (Dynamic)/----------------------------+
+-/FOR/-----------------------------------------------+
+-+-----------------------------------+--/fullselect/-+
| | .-,-----------------------. | |
| | V | | |
| '-WITH----/common-table-expression/-+-' |
+-/GET DIAGNOSTICS/-----------------------------------+
+-/IF/------------------------------------------------+
+-/INSERT/--------------------------------------------+
+-/ITERATE/-------------------------------------------+
+-/LEAVE/---------------------------------------------+
+-/MERGE/---------------------------------------------+
+-/searched-delete/-----------------------------------+
+-/searched-update/-----------------------------------+
+-/SET Variable/--------------------------------------+
+-/SIGNAL/--------------------------------------------+
'-/WHILE/---------------------------------------------'
Notes:
1. OLD and NEW can only be specified once each.
2. OLD TABLE and NEW TABLE can only be specified once each, and only
for AFTER triggers or INSTEAD OF triggers.
3. FOR EACH STATEMENT may not be specified for BEFORE triggers or
INSTEAD OF triggers.
4. WHEN condition may not be specified for INSTEAD OF triggers.
INSTEAD OF
Specifies that the associated triggered action replaces the action
against the subject view. Only one INSTEAD OF trigger is allowed for
each kind of operation on a given subject view (SQLSTATE 428FP).
WHEN (search-condition)
Specifies a condition that is true, false, or unknown. The
search-condition provides a capability to determine whether or not a
certain triggered action should be executed.
The associated action is performed only if the specified search
condition evaluates as true. If the WHEN clause is omitted, the
associated SQL-procedure statement is always performed.
The WHEN clause may not be specified for INSTEAD OF triggers
(SQLSTATE 42613).
From the PostgreSQL documentation they don't support nonstandard
instead of triggers:
CREATE TRIGGER /name/ { BEFORE | AFTER } { /event/ [ OR ... ] }
ON /table/ [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE /funcname/ ( /arguments/ )
From the Oracle documentation they do not allow a WHEN clause on an
INSTEAD OF trigger either:
*CREATE* [ *OR REPLACE* ] *TRIGGER* [ schema. ]trigger
{ *BEFORE* | *AFTER* | *INSTEAD OF* }
{ dml_event_clause
| { ddl_event [ *OR* ddl_event ]...
| database_event [ *OR* database_event ]...
}
*ON* { [ schema. ]*SCHEMA*
| *DATABASE*
}
}
[ *WHEN* (condition) ]
{ pl/sql_block | call_procedure_statement } ;
INSTEAD OF
Specify |INSTEAD| |OF| to cause Oracle Database to fire the trigger
instead of executing the triggering event. |INSTEAD| |OF| triggers are
valid for DML events on views. They are not valid for DDL or database
events.
If a view is inherently updatable and has |INSTEAD| |OF| triggers, then
the triggers take preference. In other words, the database fires the
triggers instead of performing DML on the view.
If the view belongs to a hierarchy, then the trigger is not inherited by
subviews.
Note:
Oracle Database fine-grained access control lets you define row-level
security policies on views. These policies enforce specified rules in
response to DML operations. If an |INSTEAD| |OF| trigger is also defined
on the view, then the database will not enforce the row-level security
policies, because the database fires the |INSTEAD| |OF| trigger instead
of executing the DML on the view.
INSTEAD OF Triggers
*
|INSTEAD| |OF| triggers are valid only for views. You cannot
specify an |INSTEAD| |OF| trigger on a table.
*
You can read both the :|OLD| and the :|NEW| value, but you cannot
write either the :|OLD| or the :|NEW| value.
Note:
You can create multiple triggers of the same type (|BEFORE|, |AFTER|, or
|INSTEAD| |OF|) that fire for the same statement on the same table. The
order in which Oracle Database fires these triggers is indeterminate. If
your application requires that one trigger be fired before another of
the same type for the same statement, then combine these triggers into a
single trigger whose trigger action performs the trigger actions of the
original triggers in the appropriate order.
WHEN Clause
Specify the trigger condition, which is a SQL condition that must be
satisfied for the database to fire the trigger. See the syntax
description of |condition| in Chapter 7, "Conditions"
<http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions.htm#g1077361>.
This condition must contain correlation names and cannot contain a query.
The |NEW| and |OLD| keywords, when specified in the |WHEN| clause, are
not considered bind variables, so are not preceded by a colon (:).
However, you must precede |NEW| and |OLD| with a colon in all references
other than the |WHEN| clause.
See Also:
"Calling a Procedure in a Trigger Body: Example"
<http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2064401>
Restrictions on Trigger Conditions Trigger conditions are subject to the
following restrictions:
*
If you specify this clause for a DML event trigger, then you must
also specify |FOR| |EACH| |ROW|. Oracle Database evaluates this
condition for each row affected by the triggering statement.
*
You cannot specify trigger conditions for |INSTEAD| |OF| trigger
statements.
*
You can reference object columns or their attributes, or varray,
nested table, or LOB columns. You cannot invoke PL/SQL functions
or methods in the trigger condition.
Based on this survey I would say you can do pretty much whatever you
want. I would be tempted to follow Oracle and IBM's lead and exclude
WHEN clauses on an INSTEAD OF trigger but since SQLite already accepts
it I think you are fine with your current implementation.
HTH
Dennis Cote
/||/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
