473,569 Members | 2,446 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trigger Usage?

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

Aug 29 '06 #1
2 3785
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/
Aug 29 '06 #2
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/
Aug 29 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3520
by: Neil Rutherford | last post by:
During testing of an application, i noticed a difference between SQL 2000 and SQL 7, both with identical config. In a nutshell: A table has a trigger for UPDATE and DELETE. When a column in the table is UPDATED the following happens: In autocommit mode, when entering a trigger the trancount equals 1 for both SQL 7 and 2000.
2
1692
by: lawrence | last post by:
I thought I was fighting a PHP problem, so I asked about this on a PHP list, but they felt it was a Javascript problem, so I'm reposting a bit of the debate here. Could this Javascript, below, possibly trigger a redirect in Netscape, and send the browser to a file called "0". Cause what is happening is on load, in Netscape, the page redirects...
0
2461
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as I've found it educational. Note: - I build this for use in a JDEdwards OneWorld environment. I'm not sure how generic others find it but it...
12
4743
by: Bob Stearns | last post by:
I am trying to create a duplicate prevention trigger: CREATE TRIGGER is3.ard_u_unique BEFORE UPDATE OF act_recov_date ON is3.flushes REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.act_recov_date IS NOT NULL) BEGIN ATOMIC select count(*) into v_n from is3.flushes
5
3287
by: Bob Stearns | last post by:
I have two (actually many) dates in a table I want to validate on insertion. The following works in the case of only one WHEN clause but fails with two (or more), with the (improper? inappropriate?) error message: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: CREATE TRIGGER IS3.date_later_001i NO C;BEGIN-OF-STATEMENT;<space> which is...
3
2202
by: Mae Lim | last post by:
Hi All, I've a problem here, I want to trigger a javascript function after binding the ListBox control. Basically the javascript will remove the duplicates records after recordset is bind in the ListBox control automatically without users onclick/onchange the ListBox. I don't want to distinct the records in the SQL query, because I have...
5
4272
by: marcsirois | last post by:
I am maintaining an application where most of the business rules are in Triggers, Stored Procedures and User Defined Functions. When a bug arises, it can get very tedious to debug. Today for example, I wanted to modify a function that was being called by a trigger. The problem is that I don't want to change the function, for fear that it is...
9
9299
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate...
1
3477
by: praveenc | last post by:
plse help me how to use AFTER LOGON DATABASE trigger for chnging NLs settings in the java application
0
7633
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7699
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6320
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5520
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5247
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3680
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3669
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.