SQL delimiter in triggers

DBA-Jr · #1 (**permalink**) 03-07-06, 23:58

Hi All,
I have a trigger statement like below from previous DBAs. Without changing delimiter explicitely using option -td, it does work fine everywhere. But I could not find ";--" in any IBM manuals. I still wonder why it works. Does anybody know where it is documented? Thanks.

Code:

CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
    SET NEW_ROW.my_column= current timestamp;--
END;

Marcus_A · #2 (**permalink**) 03-08-06, 01:36

; is the default delimiter.

Anytime you have a -- in an SQL statement, the remaining characters on the line are treated as comments.

DBA-Jr · #3 (**permalink**) 03-08-06, 10:44

Marcus_A,
Thank you for reply.
That's what thought. So, the "create trigger" statement should end at the following line:

Code:

SET NEW_ROW.my_column= current timestamp;--

Then, "END;" will be treated as another statement. You can not use the same delimiter for both triggered SQL (body) and the "create trigger" SQL. If you do, DB2 would parse till the first delimiter, and anything after each delimiter would be treated as another statement if it does not start with "--". That would cause syntax error. But in my case, it does NOT cause any error.

Wim · #4 (**permalink**) 03-08-06, 11:44

Code:

CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
    SET NEW_ROW.my_column= current timestamp;--
END;

Everything after the "--" until the end of the line (and including the "--") is considered comment and is not processed by DB2. So the above is the same as

Code:

CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL  -- this is my first comment
BEGIN ATOMIC
    SET NEW_ROW.my_column= current timestamp;-- this is some comment 
-- this is another line of comment
END;

and for DB2 all of the above is the same as:

Code:

CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
    SET NEW_ROW.my_column= current timestamp;
END;

DBA-Jr · #5 (**permalink**) 03-08-06, 17:29

Quote:

and for DB2 all of the above is the same as:

Code:

CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
    SET NEW_ROW.my_column= current timestamp;
END;

I would think so. The strange thing is that my original statement works, but your equivalent statement fails because DB2 can not find "END" to pair with "BEGIN ATOMIC". "END;" is truncated by the first ";".

Wim · #6 (**permalink**) 03-08-06, 17:47

Right ! It's a common problem, each statement between BEGIN ATOMIC and
END must be terminated with ';', and you have to change the terminator of the trigger (the terminator after END, the ; that couses the problem) into something else like '#'

Code:

CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
    SET NEW_ROW.my_column= current timestamp;
END#

You will have to alter Command Center's settings to another terminator character.

DBA-Jr · #7 (**permalink**) 03-08-06, 21:00

I still hope someone can explain why my original code works. It's not supposed to work according the rule. So my question is: why does it work w/o error.

DBA-Jr · #8 (**permalink**) 03-11-06, 11:58

Any explanation is appreciated.

Internet Services

Web Development

Digital Marketing

Consumer Tech