Dear DB2 community,
I have a hard time implementing a seemingly easy BEFORE INSERT trigger on a table.
Here's a part of the trigger I've implemented so far:
CREATE OR REPLACE TRIGGER ... BEFORE INSERT ON ...
REFERENCING NEW AS bs
IF ... THEN
...
ELSE
SET beleg_id_neu= next value for BUCHUNGSBELEG_ID_SEQ;
insert into buchungsbeleg values(beleg_id,1,belegnr,bs.buchh_id,null);
SET bs.beleg_id = beleg_id_neu; --This doesn't "compile"
END IF;
The problem ist the set bs.beleg_id=beleg_id_neu statement. It cannot be compiled. Trying to create the trigger results in:
The trigger "myTriggerName" is defined with an unsupported triggered SQL statement.. SQLCODE=-797, SQLSTATE=42987, DRIVER=4.16.53
When I comment the set out, everything is fine. But the Trigger doesn't do what I want ;-)
So what do I want? I want to insert a new row in some other table than the one that gets INSERTED and then add the foreign key to this new row to the inserted row. For this I use a sequence.
I am not entirely sure whether the SQL error is due to the use of SET or if it is the attempt to update the value (which I think is okay, many examples on the web do that stuff)...
Any comments, ideas, hints?
Thanks in advance and Happy New Year!
Joachim
If I understand correctly, you want your statement
SET bs.beleg_id = beleg_id_neu;
to update a row in the table. It does not do that - if it were a legal statement it would just set a local variable.
To update a row in the
database you need to use the UPDATE statement.
--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
To me it sounds like the SET option would save an UPDATE SQL Statement
and since the insert is being issued quite a few times a day, I'd like
to make it as fast as possible. So I will play with that Trigger a
little more...
Oh no, I didn't read carefully enough. You are saying my problem is not the SET, but the INSERT...?
So no way of using the SET option.
Jerry's suggestion is the only working one for my case.
On Monday, January 1, 2018 at 1:20:17 PM UTC+5, Joachim Tuchel wrote:
Oh no, I didn't read carefully enough. You are saying my problem is not the SET, but the INSERT...?
So no way of using the SET option.
Jerry's suggestion is the only working one for my case.
Еhe code is rather incomprehensible for me.
CREATE OR REPLACE TRIGGER ... BEFORE INSERT ON some_table
REFERENCING NEW AS bs
IF ... THEN
...
ELSE
SET beleg_id_neu= next value for BUCHUNGSBELEG_ID_SEQ;
insert into buchungsbeleg values(beleg_id,1,belegnr,bs.buchh_id,null);
SET bs.beleg_id = beleg_id_neu; --This doesn't "compile"
END IF;
Apparently some_table<>buchungsbeleg.
insert into buchungsbeleg values(beleg_id,1,belegnr,bs.buchh_id,null); What is beleg_id? It bs.beleg_id may be?
Why this sequence of statements?
insert into buchungsbeleg values(beleg_id,1,belegnr,bs.buchh_id,null);
SET bs.beleg_id = beleg_id_neu;
Its seems correct way is
SET bs.beleg_id = beleg_id_neu;
insert into buchungsbeleg values(bs.beleg_id,1,bs.belegnr,bs.buchh_id,null);
and then it transforming into 2 triggers:
CREATE OR REPLACE TRIGGER ... BEFORE INSERT NO CASCADE ON some_table REFERENCING NEW AS bs
IF ... THEN
...
ELSE
SET bs.beleg_id = next value for BUCHUNGSBELEG_ID_SEQ;
END IF;
CREATE OR REPLACE TRIGGER ... AFTER INSERT ON some_table
REFERENCING NEW AS bs
IF ... THEN
...
ELSE
insert into buchungsbeleg values(bs.beleg_id,1,bs.belegnr,bs.buchh_id,null);
END IF;
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 61:51:02 |
Calls: | 6,654 |
Files: | 12,200 |
Messages: | 5,331,620 |