Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Question Unanswered: Create trigger to check values at insert/update

    I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:

    ---------------------------------
    CREATE TRIGGER [trigger_checkCurrency] ON [dbo].[Client]
    FOR INSERT, UPDATE
    AS
    declare @currency as char(50)
    declare @country as char(50)

    declare cur cursor for SELECT currency, country
    FROM inserted

    OPEN cur
    fetch cur into @currency, @country
    WHILE @@FETCH_STATUS = 0
    BEGIN
    if not exists(select * from listinfoid where listname = 'currency' and listid = @currency)
    begin
    set @currency = (cast(@currency as varchar (3)) + ' is not a valid currency')
    CLOSE cur
    DEALLOCATE cur
    RAISERROR (@currency,16,-1) with log
    return
    end
    if not exists(select * from listinfoid where listname = 'country' and listid = @country)
    begin
    set @country = (cast(@country as varchar (3)) + ' is not a valid contry')
    CLOSE cur
    DEALLOCATE cur
    RAISERROR (@country,16,-1) with log
    return
    end
    else
    fetch cur into @currency, @country

    END
    CLOSE cur
    DEALLOCATE cur
    update Client set currency = UPPER(currency), country = UPPER(country)
    ---------------------------------



    I use a cursor to handle multiple rows in an update query.
    (SQL2000-server)

    Is there an easier och better way to do this?
    I´m a bit unsure of this code.

    Thanx!

    /Erik

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did this even compile?

    First...I would never (never say never as they say) use a cursor in a trigger...

    Second you're closing the cursor twice...huh?

    I don't get it..

    Third, it looks like you're just trying to establish RI (Relational Integrity) which can be done with Foreign Keys (FKs)

    Is that what you're trying to accomplish?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Brett Kaiser
    Did this even compile?


    - Yes, it works fine!



    First...I would never (never say never as they say) use a cursor in a trigger...

    - I have to use a cursor to deal with multiple updates, because
    the "inserted" table contains more than one row.
    How else should I do it?


    Second you're closing the cursor twice...huh?

    - Only once, but at different places in the code depending on
    the result of the if-statement.


    I don't get it..

    Third, it looks like you're just trying to establish RI (Relational Integrity) which can be done with Foreign Keys (FKs)

    - Well, oooops... I thought I couldn´t solve it with FKs, but
    I was possible though. :-O




    Well, I dont think I can solve

    Is that what you're trying to accomplish?

Posting Permissions

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