"INSERT OR UPDATE" command triggers both insert and update trigger
------------------------------------------------------------------
Key: CORE-3674
URL: http://tracker.firebirdsql.org/browse/CORE-3674
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.1
Reporter: Attila Molnár
Hi!
The next example is a a little complicated, sorry for that.
CREATE DATABASE OBJECTS :
CREATE GENERATOR GEN_TEST;
CREATE TABLE TESTTABLE (
ID INTEGER NOT NULL,
CODE VARCHAR(20)
);
CREATE OR ALTER TRIGGER TESTTABLE_BI FOR TESTTABLE
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null)
then new.id = gen_id(GEN_TEST, 1);
end
CREATE TABLE TESTTABLE2 (
ID INTEGER NOT NULL,
ID_TESTTABLE INTEGER,
ROWNO INTEGER,
NAME VARCHAR(20)
);
CREATE OR ALTER TRIGGER TESTTABLE2_BI FOR TESTTABLE2
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null)
then new.id = gen_id(GEN_TEST, 1);
end
CREATE OR ALTER VIEW TESTTABLE_V(
ID,
CODE)
AS
select id, code
from testtable;
CREATE OR ALTER TRIGGER TESTTABLE_V_BI FOR TESTTABLE_V
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
if (inserting)
then begin
insert into testtable(id, code)
values(new.id, new.code)
returning id
into new.id;
end
if (updating)
then begin
update testtable set
code = new.code
where id = new.id;
end
end
CREATE OR ALTER VIEW TESTTABLE2_V(
ID,
ID_TESTTABLE,
ROWNO,
NAME)
AS
select id, id_testtable, rowno, name
from testtable2;
CREATE OR ALTER TRIGGER TESTTABLE2_V_BI FOR TESTTABLE2_V
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
if (inserting)
then begin
insert into testtable2(id,id_testtable,rowno,name)
values(new.id, new.id_testtable,new.rowno,new.name)
returning id
into new.id;
end
if (updating)
then begin
update testtable2 set
id_testtable = new.id_testtable,
rowno = new.rowno,
name = new.name
where id = new.id;
end
end
CREATE OR ALTER VIEW TESTVIEW(
ID,
CODE,
T2_ID,
T2_ID_TESTTABLE,
T2_ROWNO,
T2_NAME)
AS
SELECT
t.id, t.code,
t2.id, t2.id_testtable, t2.rowno, t2.name
FROM testtable_v t JOIN
testtable2_v t2 ON t2.id_testtable = t.id;
CREATE OR ALTER TRIGGER TESTVIEW_BI FOR TESTVIEW
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
IF (INSERTING)
THEN BEGIN
insert into testtable_v(id, code) values(new.id, new.code) returning id
into new.id;
new.t2_id_testtable = new.id;
insert into testtable2_v(id, id_testtable, rowno, name)
values(new.t2_id, new.t2_id_testtable, new.t2_rowno, new.t2_name);
END
IF (UPDATING)
THEN BEGIN
update testtable_v set
code = new.code
where id = new.id;
new.t2_id_testtable = new.id;
--this works
/*
if (exists(select * from testtable2_v where id_testtable =
new.t2_id_testtable and rowno = new.t2_rowno))
then begin
update testtable2_v set
name = new.t2_name
where id_testtable = new.t2_id_testtable and rowno = new.t2_rowno;
end
else begin
insert into testtable2_v(id, id_testtable, rowno, name)
values(new.t2_id, new.t2_id_testtable, new.t2_rowno, new.t2_name);
end
*/
--this not works, makes an extra insert, beacause both insert and update
trigger fired
update or insert into testtable2_v(id, id_testtable, rowno, name)
values(new.t2_id, new.t2_id_testtable, new.t2_rowno, new.t2_name)
matching(id_testtable, rowno);
END
end
HERE COMES THE TESTS :
#1 : insert into testview(id, code, t2_rowno, t2_name) values(1, 'XXX', 1,
'blah');
Work as expected
#2 :
update testview set
t2_rowno = 1,
t2_name = 'xxx'
where id = 1
Not works as expected :
Makes an extra record insert, because both insert and update trigger fired (via
"update or inser" command on view testtable2_v in trigger TESTVIEW_BI).
(If I make an exists preselect , and then call "insert" or else "update" then
woks as expected.)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
