Post reply

Help! Comparitive Trigger Problem

  • November 23, 2003 at 1:48 am

    #83566

    Hi

    I new to SQL Server so sorry if this is an easy question. I am trying to compare the values in two columns (in the same table). I know the error is in the IF statement but I don't know how it should be written. Any help appreciated.

    CREATE TRIGGER CREDIT_CHECK ON [dbo].[Customers]

    FOR INSERT, UPDATE

    AS

    IF UPDATE (Cus_Max_Credit) OR UPDATE (Cus_Credit_Used)

    BEGIN

    IF Cus_Max_Credit < Cus_Credit_Used

    RAISERROR ('Maximum credit should always be greater than credit used', 16, 1)

    ROLLBACK TRANSACTION

    END

  • November 24, 2003 at 5:53 am

    #483318

    Three errors here:

    • Realize that updates and inserts can happen to sets rather than just discrete rows, so your trigger needs to work with a set.
    • You can't use column identifiers in a statement without referencing a table; the inserted virtual table in update and delete triggers contains the rows being modified.
    • If you have more than one statement in to be executed pursuant to an IF condition, the statements must be blocked with BEGIN...END.

    CREATE TRIGGER CREDIT_CHECK ON [dbo].[Customers]

    FOR INSERT, UPDATE

    AS

    IF UPDATE (Cus_Max_Credit) OR UPDATE (Cus_Credit_Used)

    BEGIN

    IF

    (SELECT COUNT(*)

    FROM inserted

    WHERE Cus_Max_Credit < Cus_Credit_Used) > 0

    BEGIN

    RAISERROR ('Maximum credit should always be greater than credit used', 16, 1)

    ROLLBACK TRANSACTION

    END

    END

    --Jonathan

    Edited by - Jonathan on 11/24/2003 05:53:34 AM



    --Jonathan

  • November 24, 2003 at 6:04 am

    #483321

    Try this

    IF EXISTS (SELECT name FROM sysobjects

    WHERE name = 'CREDIT_CHECK' AND type = 'TR')

    DROP TRIGGER CREDIT_CHECK

    GO

    CREATE TRIGGER CREDIT_CHECK

    ON Customers

    FOR INSERT, UPDATE

    AS

    BEGIN

    DECLARE @Cus_Max_Credit tinyint,

    @Cus_Credit_Used tinyint --declare your own data types

    SELECT @Cus_Max_Credit = Cus_Max_Credit,

    @Cus_Credit_Used = Cus_Credit_Used

    FROM inserted

    BEGIN

    IF @Cus_Max_Credit < @Cus_Credit_Used

    RAISERROR ('Maximum credit should always be greater than credit used', 16, 1)

    ROLLBACK TRANSACTION

    END

    END

    If it aint broke don't fix it!


    Andy.

  • November 24, 2003 at 6:07 am

    #483322

    Hey Jonathan - You beat me to it! - Anyway yours is better - less code!

    If it aint broke don't fix it!


    Andy.

  • November 25, 2003 at 2:21 am

    #483498

    Thanks so much!

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

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