OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » trigger (db2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

trigger [message #606456]

Fri, 24 January 2014 03:10

rickmeister
Messages: 2
Registered: January 2014
Location: Brussels

Junior Member

Hi,

I try to create a trigger in DB2 , I have following logic but not sure if DB2 is supporting variables within triggers

CREATE OR REPLACE TRIGGER TEST.TEST_ID_TRIGGER

DECLARE
v_var1 VARCHAR(5);
V_var2 VARCHAR(5) ;

BEFORE INSERT ON TEST.CHRY_TEST_CASES FOR EACH ROW
BEGIN
SELECT TEST_DOMAIN into v_var1 FROM TEST.CHRY_TEST_CASES;
SELECT TABLE_NAME into v_var1 FROM TEST.CHRY_TEST_CASES;

set test_id = v_var1||v_var2|| select lpad(max(substr(test_id,11,3))+1,3,0) from test.chry_test_cases where TEST_DOMAIN = v_var1 and TABLE_NAME = v_var2
END

Thanks,

Report message to a moderator

Re: trigger [message #606458 is a reply to message #606456]

Fri, 24 January 2014 03:24

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why do you post a DB2 question in an Oracle forum?

Report message to a moderator

Re: trigger [message #606459 is a reply to message #606456]

Fri, 24 January 2014 04:02

rickmeister
Messages: 2
Registered: January 2014
Location: Brussels

Junior Member

Thaks for the prompt reply. I guess db2 would be supporting this, howver, wrt the code I get an error message - the issue is namely that I want to set a variable in a variable Smile

CREATE OR REPLACE TRIGGER TEST.TEST_ID_TRIGGER

DECLARE
v_var1 VARCHAR(5);
V_var2 VARCHAR(5) ;

BEFORE INSERT ON TEST.CHRY_TEST_CASES FOR EACH ROW
BEGIN
SELECT TEST_DOMAIN into v_var1 FROM TEST.CHRY_TEST_CASES;
SELECT TABLE_NAME into v_var1 FROM TEST.CHRY_TEST_CASES;

set test_id = v_var1||v_var2|| select lpad(max(substr(test_id,11,3))+1,3,0) from test.chry_test_cases where TEST_DOMAIN = v_var1 and TABLE_NAME = v_var2
END

Report message to a moderator

Re: trigger [message #606463 is a reply to message #606459]

Fri, 24 January 2014 04:15

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Rick, I'll have try at doing the moderator bit:

First, welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
Second, it is not professional behaviour to ask questions about one product in a forum set up for a different product.
Third, all anyone here can tell you is "your code is not valid PL/SQL". Whether it is valid in what I think is called SQL PL, I have no idea.

You need to relate your question to Oracle, or I'll lock the topic.

Report message to a moderator

Previous Topic:	need query syntax
Next Topic:	user defined exception and init pragma exception

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 06:34:13 CDT 2024