Need help with a BEFORE INSERT Trigger and Sqeuences

[Joachim Tuchel]

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

[Jerry Stuckle]

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
jstu...@attglobal.net
==================

[Joachim Tuchel]

Jerry,

thanks a lot for answering. This really is an odd problem...

Am Sonntag, 31. Dezember 2017 02:57:43 UTC+1 schrieb Jerry Stuckle:

> If I understand correctly, you want your statement
>
> SET bs.beleg_id = beleg_id_neu;

Yes, exactly. I want to change a foreign key in the just to be inserted row to point at a row that is inserted by the trigger on insert of the row. Like "Oh, you want to insert a car, let me insert a steering wheel for you and link to it by setting the car's foreign key to this newly inserted steering wheel" - I guess you know what I mean...

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

I had found this page:
https://www.toadworld.com/platforms/ibmdb2/w/wiki/7482.changing-inserted-values

where the example Triggers do exactly that: change values during/before an insert. Did I misunderstand?

> To update a row in the
> database you need to use the UPDATE statement.

I tried that. I replaced the

set bs.beleg_id=beleg_id_neu;
with

update bs set beleg_id = beleg_id_neu where id=bs.id;


and changed the trigger to an AFTER INSERT trigger, because I guess it is not possible to update a to-be-inserted row using SQL before it exists...

The result of my first test is that the INSERT never finishes but also doesn't throw an error.

So here is my question:

If an INSERT Trigger changes the row that is triggering the Trigger (;-)), does such an update statement work at all? Will it make any difference if the Trigger is a BEFORE INSERT or an AFTER INSERT Trigger?

I am a bit confused and demotivated by my exepriments so far...


Joachim

[Joachim Tuchel]

Sorry, I overlooked an error when I tried using the UPDATE statement as suggested:

SQLSTATE=09000 - [IBM][CLI Driver][DB2/LINUXX8664] SQL0723N An error occurred in a triggered SQL statement in trigger "DB2INST1.auto_belegnr". Information returned for the error includes SQLCODE "-407", SQLSTATE "23502" and message tokens "TBSPACEID=2, TABLEID=515, COLNO=0". SQLSTATE=09000
[Native Error=-723]


It was in fact a typo in the trigger that caused a not null error.

The SQL UPDATE of the just-inserted row in fact works as expected!


I tried (and succeeded in) an AFTER INSERT Trigger, because it sounds logical to me. So Jerry's suggestion is the solution. I know I also tried that yesterday, but maybe in a BEFORE INSERT Trigger. Not sure, but it doesn't really matter for my case.

Thanks a lot Jerry! Happy New Year!

[vvm...@gmail.com]

As far i know

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;

You can't use "insert", "update" etc into "before insert" trigger.
Nothing wrong with "SET bs.beleg_id = beleg_id_neu;".

This must be correct:

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;

See https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000931.html?pos=3 .

The SQL-procedure-statement in a BEFORE trigger cannot:

Contain any INSERT, DELETE, or UPDATE operations, nor invoke any routine defined with MODIFIES SQL DATA, if it is not a compound SQL (compiled).
Contain any DELETE or UPDATE operations on the trigger subject table, nor invoke any routine containing such operations, if it is a compound SQL (compiled).
Reference a materialized query table defined with REFRESH IMMEDIATE (SQLSTATE 42997)
Reference a generated column other than the identity column in the NEW transition variable (SQLSTATE 42989).
...

[Joachim Tuchel]

So I need to research a bit more...

If I understand correctly, manipulating to-be-inserted values in a BEFORE INSERT Trigger should work using SET.

Tha other option, as suggested by Jerry, is to use an AFTER INSERT Trigger and issue another update SQL statement. I have gotten this to work.

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

Thanks for your comments

Joachim

[Joachim Tuchel]

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.

[Peter H. Coffin]

On Mon, 1 Jan 2018 00:17:51 -0800 (PST), Joachim Tuchel wrote:

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

Plus, it's a database "quite a few" transactions would have to mean like
"adding ten million transactions per hour", not "an extra ten thousand a
day". Transactions are kind of what databases DO. They're GOOD at
managing them. That's literally why you use them instead of an Excel
spreadsheet. *grin*

--
59. I will never build a sentient computer smarter than I am.
--Peter Anspach's list of things to do as an Evil Overlord

[vvm...@gmail.com]

Е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;

[Jerry Stuckle]

OK, I misunderstood what you were trying to do. I think I understand
better now.

Let's back up a bit. What is the exact trigger you're trying to create?
And what are the table definitions? A difference in either (or both)
can cause this problem.