Need help with Trigger

gourmet · #1 (**permalink**) 02-11-10, 15:59

Can anyone please help me with the following:

Have 3 tables

Table AAA (id, name, address, chk)
Table BBB (id, prod, desc)
Table CCC (Id, aaa.id, bbb.id, dis, comment(Varchar))

Want to Create a Trigger:

When a New Row is Inserted in the Table BBB, the following Action is needed

Rows are inserted in the Table CCC Values:

Max(ccc.id)+1,AAA.id, Max(bbb.id), 0, '' for all the records from table AAA where aaa.chk = 1

n_i · #2 (**permalink**) 02-11-10, 16:22

So, what is your question (or problem)?

gourmet · #3 (**permalink**) 02-11-10, 20:55

Sorry, the questions is:

I need a trigger which Adds the following to CCC Table:

Max(ccc.id)+1,AAA.id, Max(bbb.id), 0, '' for all the records from table AAA where aaa.chk = 1

stolze · #4 (**permalink**) 02-12-10, 03:32

So what's the problem? Just write an AFTER INSERT trigger operating on table B, which drives the INSERT INTO ccc SELECT ... FROM ... statement.

gourmet · #5 (**permalink**) 02-12-10, 07:36

I am beginner in SQL. I tried many format of insert statements but none worked. Please help me understand why the following does not work and what should be the correct statement:

Insert into CCC (Id, id, id, dis, comment)
Values
(Select Max(id)+1 from ccc, 463,3023,0,'');

Insert into CCC
select
Max(c.id)+1,a.id , Max(b.id), 0, ''
from aaa a, bbb b, ccc c
Where c.chk = 1

Bottomline is: I am not able to write the correct SQL to Execute the Insert Trigger

Thanks,

gourmet · #6 (**permalink**) 02-12-10, 16:10

anyone pls help !!!

n_i · #7 (**permalink**) 02-12-10, 17:38

I think that, before you can approach triggers, you need to refresh basic SQL queries and join types (e.g. here DB2 SQL Cookbook), then figure out correct INSERT statement syntax (https://publib.boulder.ibm.com/infoc.../t0021844.html). Once this is out of the way, the trigger part should be easy.

stolze · #8 (**permalink**) 02-15-10, 08:16

Quote:

Originally Posted by gourmet

Insert into CCC (Id, id, id, dis, comment)
Values
(Select Max(id)+1 from ccc, 463,3023,0,'');

This is indeed a basic SQL issue you're facing here. What you tell the database system (DB2) to do is to select the maximum of column ID from table CCC joined with 463 joined with 3023 joined with 0 joined with ''. That is, of course, a syntax error already. If you want to use a scalar subselect, you have to put it into parenthesis.

Quote:

Insert into CCC
select
Max(c.id)+1,a.id , Max(b.id), 0, ''
from aaa a, bbb b, ccc c
Where c.chk = 1

This statement looks syntactically correct. But why you need a 3-way join here whereas the above query didn't, I do not know.

gourmet · #9 (**permalink**) 02-16-10, 19:39

Thanks Nick for the documentation. I shall go through these and make my base solid.

DBFinder · #10 (**permalink**) 02-18-10, 21:26

Quote:

Originally Posted by gourmet

anyone pls help !!!

Helping . . .

Whatever I have understood, I have written an example.

Tables:

Code:

Create table AAA(id int,name char(15), address varchar(30),chk smallint);
Create Table BBB(id int, prod varchar(15), desc varchar(100));
Create Table CCC (id int, aaaid int, bbbid int, dis decimal(10,2), comment Varchar(100));

insert into AAA values (1,'gourmet','xxx Some Street,Anycity',1),(2,'gourmet friend','yyy New Lane,OldCity', 1),(3,'Good Man','zzz Dark Boulevard,Bright City', 2);
insert into BBB values (10, 'xxxxxxxxx', 'No details found'),(11, 'yyyyyy', 'behind the dark');
insert into CCC Values (36,2,11, 203.55,'Don''t understand this mess');

Trigger:

Code:

CREATE TRIGGER AfterInsert
	    AFTER INSERT ON BBB
	 	FOR EACH ROW 
	    MODE DB2SQL
begin atomic

insert into ccc select (select max(id) from ccc)+1,id,(select max(id) from bbb),0,'' from aaa where aaa.chk=1;

end
@

Run :

Code:

D:\SGILL\Dev>db2 -td@ -f trigger.sql
DB20000I  The SQL command completed successfully.


D:\SGILL\Dev>db2 insert into BBB values (21, 'AAAAAAAAA', 'TEST ENTRY')
DB20000I  The SQL command completed successfully.

D:\SGILL\Dev>DB2 SELECT * FROM CCC

ID          AAAID       BBBID       DIS          COMMENT

----------- ----------- ----------- ------------ ---------------------------------------------------
-------------------------------------------------
         36           2          11       203.55 Don't understand this mess

         37           1          21         0.00

         37           2          21         0.00


  3 record(s) selected.

I might not have fully understood your logic, this example for sure will help you.

Hoping

DBFinder

DBFinder · #11 (**permalink**) 02-18-10, 22:13

I have posted the example, you may tailor it to your needs.

HTH

Internet Services

Web Development

Digital Marketing

Consumer Tech