Hi all, any comments on this one would be much appreciated:

Background: somewhere in the enterprise, very occasionally, an invalid
record is being created in a table that shouldn't be there and causes
a lot of problems.

I want to add an insert trigger that undoes this record, and sends out
an alert.

The trigger below compiles fine, and sends the alert, but an insert to
the table never seems to return. I'm sure I have some sort of locking
or transaction issue.

Here is the create trigger statement:

create trigger mpdta/f3111chk
after insert on mpdta/f3111
referencing new row as nr
for each row
when (nr.wmukid = 0)
begin
call qsys2/qcmdexc('SNDDST TYPE(*LMSG) TOUSRID((INTERNET SMTPRTE))
TOINTNET((jxxxx.yyyyyy (AT) xxxxx (DOT) xxx.xx)) DSTD(''F3111 Alert'')
LONGMSG(''A zero WMUKID was inserted'')', 0000000141.00000);
delete from mpdta/f3111
where nr.wmukid = 0;
end

If I siimply run:
insert into mpdta/f3111 (wmukid) values(0);

The insert never returns (but I do get the email).

So first of all, is there a smarter way to 'undo' the insert by
failing the trigger? Otherwise can I change the transaction control so
it won't tie itself in knots?

This is an iSeries v6t1 running (I thinik) db2 udb for i.

Thanks very much in advance for any suggestions,

Regards
JohnO

On Mar 4, 9:11*am, JohnO <johno1... (AT) gmail (DOT) com> wrote:
BTW, I tried to use the SIGNAL command in a before-trigger as I hoped
that would error out the transaction before it happened but get an
error that SQL updates are not allowed in a before trigger.

On Mar 4, 9:38*am, JohnO <johno1... (AT) gmail (DOT) com> wrote:
By the way, a CHECK CONSTRAINT would be a great idea - but I do still
want to get the alert to help find the cause of the issue in teh first
place.

On 2011-03-03 22:11, JohnO wrote:
I know nothing about db2 for i, but for LUW you could do (using @ as
stmt terminator):

create trigger mpdta/f3111chk
before insert on mpdta/f3111
referencing new as n
for each row
when (nr.wmukid = 0)
signal SQLSTATE '....' ('Invalid wmukid')
@

It's probably a good idea to add the check constraint as well


/Lennart