If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > trigger problem

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-31-10, 05:11
anuj_khanna anuj_khanna is offline
Registered User
 
Join Date: Aug 2010
Posts: 5
Red face trigger problem

am using ERwin data modeler to design the er diagram of my database
and by using forward engineering feature i generated the database but some triggers are not running ... please help me out

the triggers are

CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> :new.city_id) > 0)
AND
((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0))


CREATE TRIGGER tU_cust_info NO CASCADE BEFORE UPDATE ON cust_info
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (((SELECT count(*) FROM cust_info WHERE cust_info.cust_id <> :new.cust_id) > 0) AND
((SELECT count(*) FROM taxi_booking_details WHERE taxi_booking_details.cust_id = old.cust_id) > 0))


CREATE TRIGGER tU_taxi_booking_de NO CASCADE BEFORE UPDATE ON taxi_booking_details
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN ((SELECT count(*) FROM taxi_booking_details WHERE :new.tx_bukng_id <> :old.tx_bukng_id) > 0)
UPDATE taxi_booked
SET
taxi_booked.tx_bukng_id = new.tx_bukng_id
WHERE
taxi_booked.tx_bukng_id = old.tx_bukng_id


CREATE TRIGGER tU_taxi_details NO CASCADE BEFORE UPDATE ON taxi_details
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id <> :new.city_id
OR
taxi_details.tx_id <> :new.tx_id) > 0)
AND
((SELECT count(*) FROM taxi_booked WHERE taxi_booked.city_id = old.city_id
AND
taxi_booked.tx_id = old.tx_id) > 0))

Last edited by anuj_khanna; 08-31-10 at 05:18.
Reply With Quote
  #2 (permalink)  
Old 08-31-10, 07:45
tonkuma tonkuma is online now
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 2,178
What are the DDLs of tables(city_details, taxi_details, cust_info, taxi_booking_details, taxi_booked) and indexes.

Quote:
... but some triggers are not running ...
How did you know some are not running?
If you got some error/warning message(s), please show error codes and full message texts.
Reply With Quote
  #3 (permalink)  
Old 09-01-10, 04:02
stolze stolze is offline
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
What's the point of all the triggers, except the 3rd? They just have a WHEN clause but nothing in the trigger body. So there is no triggered action at all.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #4 (permalink)  
Old 09-01-10, 05:01
anuj_khanna anuj_khanna is offline
Registered User
 
Join Date: Aug 2010
Posts: 5
@TONKUMA

the error message of first trigger is as follows>>>

------------------------------ Commands Entered ------------------------------
CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> :new.city_id) > 0) AND
((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0));
------------------------------------------------------------------------------
CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> :new.city_id) > 0) AND
((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0))
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ":new" was found following "y_details.city_id
<>". Expected tokens may include: "<name>". LINE NUMBER=3. SQLSTATE=42601

SQL0104N An unexpected token ":new" was found following "y_details.city_id <>". Expected tokens may include: "<name> ".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response:

Examine and correct the statement in the area of the specified token.

sqlcode: -104

sqlstate: 42601
Reply With Quote
  #5 (permalink)  
Old 09-01-10, 05:02
anuj_khanna anuj_khanna is offline
Registered User
 
Join Date: Aug 2010
Posts: 5
@stolze

thank you sir,

then what's the problem in the third one?
Reply With Quote
  #6 (permalink)  
Old 09-01-10, 07:34
tonkuma tonkuma is online now
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 2,178
anuj_khanna

Error message gave you enough information.
Quote:
SQL0104N An unexpected token ":new" was found following "y_details.city_id <>". Expected tokens may include: "<name> ".
":new" is invalid. Remove colon(":").

You should study basic of SQL on DB2 by using some entry books.
Reply With Quote
  #7 (permalink)  
Old 09-01-10, 07:45
n_i n_i is offline
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,448
Quote:
Originally Posted by anuj_khanna View Post
------------------------------ Commands Entered ------------------------------
CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> :new.city_id) > 0) AND
((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0));
------------------------------------------------------------------------------
What is the purpose of an empty trigger? It does not seem to do anything, and I doubt it will compile.
Reply With Quote
  #8 (permalink)  
Old 09-01-10, 07:48
anuj_khanna anuj_khanna is offline
Registered User
 
Join Date: Aug 2010
Posts: 5
@tonkuma

sir/madam

i have already tried that. but there was an error and that was>>>

------------------------------ Commands Entered ------------------------------
CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> new.city_id) > 0) AND
((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0));
------------------------------------------------------------------------------
CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> new.city_id) > 0) AND
((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0))
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "=
old.city_id) > 0))". Expected tokens may include: "<combined_trigger_body>".
LINE NUMBER=4. SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "= old.city_id) > 0))". Expected tokens may include: "<combined_trigger_body> ".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response:

Examine and correct the statement in the area of the specified token.

sqlcode: -104

sqlstate: 42601
Reply With Quote
  #9 (permalink)  
Old 09-01-10, 09:42
tonkuma tonkuma is online now
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 2,178
anuj_khanna,

PLEASE read replies of stolze and n_i.
Reply With Quote
  #10 (permalink)  
Old 09-01-10, 12:39
anuj_khanna anuj_khanna is offline
Registered User
 
Join Date: Aug 2010
Posts: 5
to all

sorry for lesser information.... actually am novice in databases and started with DB2 ... i posted my question here cause one one can easily got solution of its problems by posting on such forums.

i got solution of three of my problems.

but what is the problem with 3rd one trigger?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On