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-automations opened this issue Nov 22, 2005 · 15 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

Fix Version: 3.0 [ 10048 ]

SF_ID: 1363616 =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 2.1 [ 10041 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

SF_ID: 1363616 =>

Fix Version: 3.0 [ 10048 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

SF_ID: 1363616 =>

@firebird-automations
Copy link
Collaborator Author

Commented by: lacak (lacak)

Great . Thank you !

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

SF_ID: 1363616 =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Fix Version: 2.1 Alpha 1 [ 10150 ]

SF_ID: 1363616 =>

Fix Version: 2.1.0 [ 10041 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA127 [ QA127 ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Q/A tested

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

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
Development

No branches or pull requests

2 participants