Trigger/Check contraint implementation (urgent)

• R.B.Verma

  SSC Enthusiast

  Points: 158

  More actions

  August 30, 2004 at 6:28 am

  #108596

  Hi ,

  I have to write trigger or it may be check constraint for the following condition..in IBM DB2 databse

  I have two tables name bid and aut_attr,both the table is relational having bid_id column as PK and FK.

  bid table               aut_attr table

  ---------               --------------

  aut_id  bid_price    aut_id    min_bid_price

  ====  =====    =====  =============

    1          105           1           100

    2          250           2           200

    3          310           3           300

    4          499           4           400 

  In aut_attr table another column min_bid_price is there have a fixed price of 100,200,300,400.

  write a trigger for inserting bid_price which is not less than min_bid_price value in aut_attr table.So at the time of inserting into bid table it must check the min_bid_price value according to their aut_id.

   

• Bert De Haes

  Hall of Fame

  Points: 3104

  More actions

  August 31, 2004 at 1:21 am

  #521160

  Please try this :

  use tempdb -- for testing

  go

  create table dbo.bid     ( aut_id int primary key, bid_price money )

  create table dbo.aut_attr ( aut_id int primary key, min_bid_price money )

  go

  insert dbo.aut_attr values ( 1,100 )

  insert dbo.aut_attr values ( 2,200 )

  insert dbo.aut_attr values ( 3,300 )

  insert dbo.aut_attr values ( 4,400 )

  go

  create trigger bid_ins

  on dbo.bid

  for insert, update

  as

  begin

   if exists ( select 1 from inserted I, dbo.aut_attr A where I.aut_id = A.aut_id and I.bid_price < A.min_bid_price )

   begin

    RAISERROR ('bid_price should be >= min_bid_price', 16, 1)

    rollback transaction

   end

  end

  go