Post reply

Trigger

  • March 15, 2012 at 10:31 am

    #253063

    create table product1

    (

    productid int,

    productname varchar(20),

    costprice int,

    sellingprice int

    )

    My trigger should not allow to insert such a DATA whose costprice is greater then sellingprice.

    create trigger abcd1

    on product1 for insert

    as

    declare @cp int, @sp int

    select @cp = costprice, @sp = sellingprice from inserted

    if @cp > @sp

    rollback tran

    -following insert command, data donot insert but show an error

    Msg 3609, Level 16, State 1, Line 1

    The transaction ended in the trigger. The batch has been aborted.

    insert into product1 values('3','cup', 34,21)

    --following insert command data will insert but still show an error

    Msg 3609, Level 16, State 1, Line 1

    The transaction ended in the trigger. The batch has been aborted.

    insert into product1 values('4','mouse', 50,51)

  • March 15, 2012 at 10:38 am

    #1459993

    Why not use a table constraint where SellingPrice > CostPrice?

  • March 15, 2012 at 10:38 am

    #1459994

    first, you don't need a trigger...you could simply add a check constraint to prevent that.

    ALTER TABLE product1 ADD CONSTRIANT CK_COST_VS_SELL CHECK(costprice <= sellingprice)

    your trigger assumes only one row being inserted, instead, it should check all rows in the insert, and reject the insert if even one mistake exists.

    create trigger abcd1

    on product1 for insert

    as

    if EXISTS(SELECT 1 FROM inserted WHERE costprice > sellingprice)

    rollback tran

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 15, 2012 at 10:42 am

    #1460000

    Thanks, it work with constraint.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply