Triggers refining a statement

[JIH2000]

Hi,

I'm having a bit of trouble getting the syntax correct for DB2, please can someone help.
Here is the statement i want to add:

CREATE TRIGGER archive
	AFTER INSERT ON order
	REFERENCING NEW AS new
	FOR EACH ROW MODE DB2SQL
	DECLARE max INTEGER;
BEGIN
	SELECT COUNT(*) INTO max
	FROM order
	WHERE cno = :new..cno;
	IF max = 50
	INSERT INTO order_archive (oa_cno, oa_bno, oa_when, oa_qty)
	VALUES (order.cno, order.bno, order.when, order.qty)
       DELETE FROM order
	END IF;
END;

As you can see, i'm trying to archive the order table when there's 50 entries and then empty it.

Any help is appreciated )

Cheers,

Jim

 

[sathyarams]

Can you please say what error message you get ...

Cheers

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[sathyarams]

Can you please say what error message you get ...

Cheers

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[JIH2000]

Hi, the error message i get is:

SQL0104N An unexpected token "max" was found following "MODE DB2SQL DECLARE".

Expected tokens may include: &quot;JOIN <joined_table>&quot;. SQLSTATE=42601

I'll also check out that link you provided,

Thanks,

Jim

 

[blom0344]

Jim,

Just a shot at it, but chances are DB2 regards 'max'as a reserved word. Try maxx for instance to check if I am right.

T. Blom
Information analyst
tbl@shimano-eu.com

 

[tzamora]

Don't you need to say &quot;:max&quot; instead just &quot;max&quot;

http://programmer-by-the-hour.com

 

[JIH2000]

Thanks for the tip T.Blom but unfortunately that doesn't work, nice idea though. I tried replacing max with line_count in each case, but i got the same error:

During SQL processing it returned:
SQL0104N An unexpected token &quot;line_count&quot; was found following &quot;MODE DB2SQL DECLARE&quot;. Expected tokens may include: &quot;JOIN <joined_table>&quot;.
SQLSTATE=42601

mabee the declare command needs some other form of formatting...

any more ideas on how to re-write this statement?

Cheers,

Jim

 

[sathyarams]

May be, the usual delimiter problem when using triggers :

Create a file, say, trigger.sql (Note the delimiter @ at the end)

CREATE TRIGGER archive
AFTER INSERT ON order
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
DECLARE max INTEGER;
BEGIN
SELECT COUNT(*) INTO max
FROM order
WHERE cno = :new..cno;
IF max = 50
INSERT INTO order_archive (oa_cno, oa_bno, oa_when, oa_qty)
VALUES (order.cno, order.bno, order.when, order.qty)
DELETE FROM order
END IF;
END
@


From the command line
db2 -td@ -f trigger.sql

Check if this works

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[JIH2000]

thanks sathyarams, i just tried what you suggested, however it's suggseting a FROM clause is expected, although why i don't know... but at least it's a different error!

C:\PROGRA~1\SQLLIB\BIN>db2 -td@ -f G:\Documents\SYS\MB21\Re-assessment\trigger.sql

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 &quot;DECLARE max INTEGER; BEGIN SELECT C&quot; was found following &quot;EACH ROW MODE DB2SQL&quot;. Expected tokens may include:
&quot;<from_clause>&quot;. SQLSTATE=42601

 

[berlak]

Here is the working solution:

CREATE TRIGGER archive
AFTER INSERT ON order
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE v_max INTEGER;
SET v_max = (SELECT COUNT(*)FROM order WHERE cno = new.cno);
IF v_max = 50 THEN
INSERT INTO order_archive (oa_cno, oa_bno, oa_when, oa_qty)
VALUES (order.cno, order.bno, order.when, order.qty);
DELETE FROM order;
END IF;
END
@

Comments:
1. instead of &quot;select into&quot;, use &quot;set&quot;;
2. instead of max, use v_max;
3. insead of :new..cno, use new.cno
4. first line should be &quot;BEGIN ATOMIC&quot; in the triggered event
block ...
5. if ... THEN ... end if;

Result : DB20000I The SQL command completed successfully.

Sincerely,

Zsolt G. Berlak

M.Sc. in Information Engineering
Sun Certified Java Programmer
Sun Certified J2EE Developer
Sun Certified Enterprise Architect
IBM Certified Software Specialist
IBM Certified e-Business Professional (Solution Designer)
IBM Certified Database Associate

 

[blom0344]

Zsolt,

May I conclude I was right about 'max' being a reserved word in this respect?

PS, nice list of qualifications....

T. Blom
Information analyst
tbl@shimano-eu.com