Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Unanswered: Need help with Trigger

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, what is your question (or problem)?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    6
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So what's the problem? Just write an AFTER INSERT trigger operating on table B, which drives the INSERT INTO ccc SELECT ... FROM ... statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2010
    Posts
    6
    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,

  6. #6
    Join Date
    Feb 2010
    Posts
    6
    anyone pls help !!!

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by gourmet View Post
    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.

    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Feb 2010
    Posts
    6
    Thanks Nick for the documentation. I shall go through these and make my base solid.

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by gourmet View Post
    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
    Last edited by DBFinder; 02-18-10 at 23:10.

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    I have posted the example, you may tailor it to your needs.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •