Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

make syntax for "create trigger" compliant with SQL2003 [CORE711] #1083

Closed
firebird-issue-importer opened this issue Nov 22, 2005 · 15 comments
Closed

Comments

@firebird-issue-importer

Submitted by: lacak (lacak)

Jira_subtask_outward CORE2489
Is related to QA127

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.

Commits: 570b6cd

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 14, 2006

Commented by: Alice F. Bird (firebirds)

Date: 2005-11-22 12:02
Sender: lacak
Logged In: YES
user_id=1189292

MySQL:
------
CREATE TRIGGER <trigger name>
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE }
ON <table name>
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 <trigger name>
ON <table name>
FOR INSERT , DELETE , UPDATE
AS
BEGIN
...
END

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

Each DBMS accepts syntax :
"ON <table name>", but current FB "FOR <table name>"

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 14, 2006

Commented by: Alice F. Bird (firebirds)

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 19, 2006

Modified by: Alice F. Bird (firebirds)

description: 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.

=>

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 11, 2006

Modified by: @dyemanov

priority: Major [ 3 ] => Trivial [ 5 ]

Fix Version: 3.0 [ 10048 ]

SF_ID: 1363616 =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2006

Modified by: @asfernandes

Fix Version: 2.1 [ 10041 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

SF_ID: 1363616 =>

Fix Version: 3.0 [ 10048 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 4, 2006

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

SF_ID: 1363616 =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 1, 2006

Commented by: lacak (lacak)

Great . Thank you !

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 19, 2006

Modified by: @dyemanov

issuetype: New Feature [ 2 ] => Improvement [ 4 ]

SF_ID: 1363616 =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 26, 2007

Modified by: @pcisar

Fix Version: 2.1 Alpha 1 [ 10150 ]

SF_ID: 1363616 =>

Fix Version: 2.1.0 [ 10041 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2007

Modified by: @pcisar

Link: This issue is related to QA127 [ QA127 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 9, 2007

Commented by: lacak (lacak)

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)

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

Workflow: jira [ 10735 ] => Firebird [ 15122 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2008

Commented by: @pmakowski

Q/A tested

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2008

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants