Trigger Usage?

[mghale]

Hello,

I have to create a trigger to accomplish the following:

Before the insert into table A occurs, the trigger must check to see if
the combination of two columns (from the insert statement) exist in
another domain table. If the combination does not exist the trigger
must insert a record into the domain table so that the original insert
statement will succeed. I know what you're thinking, why not use RI.
Well not my choice so I'm having to try to make do with what's there...

We're on DB2 LUW 8.2 on AIX 5L.

I have this much working...

CREATE TRIGGER schema.trgrbi BEFORE INSERT ON
schema.table1 REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN
(NOT EXISTS (SELECT * FROM schema.table2 AS SG
WHERE SG.col1 = NEW.col1
AND SG.col2 = NEW.col2
AND SG.col3= 'NDS'))
SIGNAL SQLSTATE '70001' (Works')
The above trigger DDL works perfect. However when I replace the SIGNAL
SQLSTATE '70001' (Works') with the following...

INSERT INTO schema.table2
(col3,
col1,
col2,
col4)
VALUES
('NDS',
NEW.col1,
NEW.col2,
NEW.col1)

When I try to create the trigger DB2 returns a SQL0797N. However, if I
change the trigger to be an AFTER trigger, it creates successfully.

Any suggestions on how to accomplish this task in a BEFORE trigger?

Any repsonses are greatly appreciated!

Martin

[Serge Rielau]

mghale wrote:

Hello,

I have to create a trigger to accomplish the following:

Before the insert into table A occurs, the trigger must check to see if
the combination of two columns (from the insert statement) exist in
another domain table. If the combination does not exist the trigger
must insert a record into the domain table so that the original insert
statement will succeed. I know what you're thinking, why not use RI.
Well not my choice so I'm having to try to make do with what's there...

We're on DB2 LUW 8.2 on AIX 5L.

I have this much working...

CREATE TRIGGER schema.trgrbi BEFORE INSERT ON
schema.table1 REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN
(NOT EXISTS (SELECT * FROM schema.table2 AS SG
WHERE SG.col1 = NEW.col1
AND SG.col2 = NEW.col2
AND SG.col3= 'NDS'))
SIGNAL SQLSTATE '70001' (Works')
The above trigger DDL works perfect. However when I replace the SIGNAL
SQLSTATE '70001' (Works') with the following...

INSERT INTO schema.table2
(col3,
col1,
col2,
col4)
VALUES
('NDS',
NEW.col1,
NEW.col2,
NEW.col1)

When I try to create the trigger DB2 returns a SQL0797N. However, if I
change the trigger to be an AFTER trigger, it creates successfully.

Any suggestions on how to accomplish this task in a BEFORE trigger?

You can't do it in a before triggers. Before triggers don't allow
database changes.
What you could do is to define a view on the base table (SELECT * FROM T
will do).
Then define an INSTEAD OF INSERT TRIGGER on the view.
That will allow you to "have your cake and eat it to" :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[mghale]

Thanks for the reply! That was the response I was expecting based on
the behavior during testing but just wanted to double check.

Thanks again!!
Martin

Serge Rielau wrote:

mghale wrote:

Hello,

I have to create a trigger to accomplish the following:

Before the insert into table A occurs, the trigger must check to see if
the combination of two columns (from the insert statement) exist in
another domain table. If the combination does not exist the trigger
must insert a record into the domain table so that the original insert
statement will succeed. I know what you're thinking, why not use RI.
Well not my choice so I'm having to try to make do with what's there...

We're on DB2 LUW 8.2 on AIX 5L.

I have this much working...

CREATE TRIGGER schema.trgrbi BEFORE INSERT ON
schema.table1 REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN
(NOT EXISTS (SELECT * FROM schema.table2 AS SG
WHERE SG.col1 = NEW.col1
AND SG.col2 = NEW.col2
AND SG.col3= 'NDS'))
SIGNAL SQLSTATE '70001' (Works')
The above trigger DDL works perfect. However when I replace the SIGNAL
SQLSTATE '70001' (Works') with the following...

INSERT INTO schema.table2
(col3,
col1,
col2,
col4)
VALUES
('NDS',
NEW.col1,
NEW.col2,
NEW.col1)

When I try to create the trigger DB2 returns a SQL0797N. However, if I
change the trigger to be an AFTER trigger, it creates successfully.

Any suggestions on how to accomplish this task in a BEFORE trigger?

You can't do it in a before triggers. Before triggers don't allow
database changes.
What you could do is to define a view on the base table (SELECT * FROM T
will do).
Then define an INSTEAD OF INSERT TRIGGER on the view.
That will allow you to "have your cake and eat it to" :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/