Issue Details (XML | Word | Printable)

Key: CORE-711
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Trivial Trivial
Assignee: Adriano dos Santos Fernandes
Reporter: lacak
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

make syntax for "create trigger" compliant with SQL2003

Created: 22/Nov/05 12:00 AM   Updated: 07/Apr/08 11:01 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 2.1 Alpha 1

Time Tracking:
Issue & Sub-Tasks
Issue Only
Not Specified

Issue Links:
Relate
 

SF_ID: 1363616

Sub-Tasks  All   Open   

 Description  « Hide
SFID: 1363616#
Submitted By: lacak

Would it possible to change sql syntax for "create
trigger" to be compliant with SQL2003 Standard ?

SQL2003:
--------
CREATE TRIGGER <trigger name>
 {BEFORE | AFTER}
 {INSERT | DELETE | UPDATE [ OF <trigger column
list> ]}
ON <table name>
[ REFERENCING <transition table or variable list> ]
[ FOR EACH { ROW | STATEMENT } ]
[ WHEN <left paren> <search condition> <right paren> ]
<triggered SQL statement>

Current FB:
-----------
CREATE TRIGGER <trigger name> FOR <table name>
 [ACTIVE | INACTIVE]
 {BEFORE | AFTER} <multiple_action>
 [POSITION number]
AS trigger_body

Of course current FB extension {INSERT OR DELETE OR
UPDATE} is very useful.

Future FB ?:
------------
CREATE TRIGGER <trigger name>
 {BEFORE | AFTER}
 <multiple_action>
 ON <table name>
AS trigger_body

Laco.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:41 AM
Date: 2005-11-22 12:02
Sender: lacak
Logged In: YES
user_id=1189292

MySQL:
------
CREATE TRIGGER &lt;trigger name&gt;
 {BEFORE | AFTER }
 {INSERT | DELETE | UPDATE }
ON &lt;table name&gt;
FOR EACH ROW
BEGIN
 ...
END

PostgreSQL:
-----------
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )

MSSQL:
------
CREATE TRIGGER &lt;trigger name&gt;
ON &lt;table name&gt;
FOR INSERT , DELETE , UPDATE
AS
BEGIN
 ...
END

Oracle:
-------
CREATE TRIGGER &lt;trigger name&gt;
 {BEFORE | AFTER | INSTEAD OF}
 {INSERT OR DELETE OR UPDATE }
ON &lt;table name&gt;
[REFERENCING ...]
[FOR EACH ROW]
[WHEN ...]

Each DBMS accepts syntax :
"ON &lt;table name&gt;", but current FB "FOR &lt;table name&gt;"

Alice F. Bird added a comment - 14/Jun/06 09:41 AM
Date: 2005-11-22 12:02
Sender: lacak
Logged In: YES
user_id=1189292

Your Feature Request has already been been recorded in the
tracker database. Accordingly, your request has been deleted,
since it is a duplicate request.

Additionally, we currently don't use the SF Feature Request
manager. So if you have any further feature requests, they
should be posted to the SF bug tracker, and assigned to
the "Feature Request" Group.

Thanks for your interest in the project.

lacak added a comment - 01/Dec/06 01:56 AM
Great . Thank you !

lacak added a comment - 09/Aug/07 05:50 AM
Looking again at SQL:2003 and current implementation in FB 2.1, I see there a little difference :

SQL:2003 does not specify "AS" before <triggered SQL statement>

So I suggest change it slightly :
 CREATE TRIGGER <trigger name>
  {BEFORE | AFTER}
  {INSERT | UPDATE | DELETE [ | OR {INSERT | UPDATE | DELETE} ...]}
 [POSITION n]
 ON <table name>
 [FOR EACH ROW]
 <sql statement or sql block>

1. Omit "AS" or make it optional
2. add optional "FOR EACH ROW"

These changes will make syntax of CREATE TRIGGER much more close to SQL:2003 and also to other implementations (Oracle, PostgreSQL,MySQL)

Philippe Makowski added a comment - 07/Apr/08 11:01 AM
Q/A tested