Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Unanswered: Using TRIGGER to un-INSERT.

    We have to create a trigger which is activted during an INSERT which will under certain criteria NOT INSERT the row. I.E. For table T1 if C1='X' and C2='Y' do not insert the row.

    Is there a better way to do this? As you'll see below, we're allowing the insert, then deleting that row. I'd thing there's a better way.

    We're testing the following code:

    create trigger inst1.tr1
    after insert on inst1.tab1
    referencing new as new
    for each row mode db2sql
    when (new.action='C' and
    (new.elem like '%aa%' or
    new.elem like '%bb%' or
    new.elem like '%cc%' or
    new.elem like '%dd%' or
    new.elem like '%eePI%'
    )
    )
    begin atomic
    delete from inst1.tab1
    where loadday_tmstmp=new.loadday_tmstmp and
    issueno=new.a and
    itemno=new.b and
    action=new.c and
    elem_changed=new.d;
    end

  2. #2
    Join Date
    Aug 2003
    Posts
    106
    Assuming C1 and C2 is in the same table you can create a check constraint on the table which will prevent inserts using the business rule - See the SQL reference for check constraint syntax

    You are the creator of your own destiny!

Posting Permissions

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