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

Allow trigger-context variables (INSERTING, UPDATING or DELETING) to be used as parameter for SP called from Trigger [CORE4364] #4686

Open
firebird-automations opened this issue Mar 13, 2014 · 2 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

set term ^;
create or alter procedure sp_ttt as begin end^
set term ;^

recreate table ttt(id int, x int);
commit;
set term ^;
create or alter procedure sp_ttt(is_deleting boolean) as
begin
end^
create or alter trigger ttt_biud for ttt
active before insert or update or delete position 0 as
begin
--execute procedure sp_ttt( deleting ); ------------------ this causes error during compilation:
--execute procedure sp_ttt( :deleting ); ------------------ this also causes error (note on COLON before "deleting" :))
execute procedure sp_ttt( iif(deleting, true, false) ); ----- this compiled OK; but this expression looks cumbersome because 'deleting' is already of BOOLEAN type
end^
set term ;^

/*
PS. Error text:
------------------
Column does not belong to referenced table.
unsuccessful metadata update.
unknown ISC error 336397272.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
DELETING.
*/

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

summary: Allow pass boolean trigger-context variables: INSERTING, UPDATING or DELETING - directly as arguments when call SP from trigger => Allow trigger-context variables (INSERTING, UPDATING or DELETING) to be used as parameter for SP called from Trigger

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Behavior is bad, but is documented:

6\. For compatibility reasons, the names INSERTING, UPDATING and DELETING \(which aren't keywords\)
   work \(like in 2\.5 and previous\) as boolean expressions and work as values \(column or
   variable\) in non\-booleans expressions\. For example, in:
   SELECT INSERTING, NOT INSERTING FROM TEST WHERE INSERTING AND INSERTING IS TRUE
   the INSERTING's are respectively recognized as value, keyword, keyword and value\.

There is no way to make it proper without reserve the words INSERTING, UPDATING and DELETING.

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

1 participant