Results 1 to 6 of 6

Thread: trigger

  1. #1
    Join Date
    Mar 2008
    Posts
    10

    Unanswered: trigger

    i am trying to create a trigger which fire after update.the trigger should not allow the quantity in stoke from table product be less than 10
    i have used the following command but it wont work
    any idea?

    CREATE trigger Product_validation on tbl_product
    for update
    as
    if(select instoke from inserted )<
    (select instoke from tbl_product where instoke<10)
    (select instoke from deleted )
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR ('item quantity should be at least 10', 16, 10)
    END





    tnx

  2. #2
    Join Date
    Mar 2008
    Posts
    10
    i have edited the code as follow

    CREATE trigger Product_validation on tbl_product
    for update
    as
    if(select instoke from deleted )< instoke<10

    (select instoke from deleted )
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR ('item quantity should be at least 10', 16, 10)
    END

    but my problem is this part

    if(select instoke from deleted )< instoke<10

    and this is the part that it dont work

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    It would be easier to use a check constraint to do this:

    ALTER TABLE tbl_product
    ADD CONSTRAINT ck_product_instoke
    CHECK (instoke >= 10);

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Example trigger code. The check constraint is a much better option in my opinion. Use triggers only where you have to.

    CREATE TRIGGER Product_validation ON tbl_product
    FOR INSERT, UPDATE AS
    IF EXISTS
    (SELECT 1
    FROM inserted
    WHERE instoke<10)
    BEGIN;
    ROLLBACK TRANSACTION;
    RAISERROR ('item quantity should be at least 10', 16, 10);
    END;

  5. #5
    Join Date
    Mar 2008
    Posts
    10
    Quote Originally Posted by dportas
    Example trigger code. The check constraint is a much better option in my opinion. Use triggers only where you have to.

    CREATE TRIGGER Product_validation ON tbl_product
    FOR INSERT, UPDATE AS
    IF EXISTS
    (SELECT 1
    FROM inserted
    WHERE instoke<10)
    BEGIN;
    ROLLBACK TRANSACTION;
    RAISERROR ('item quantity should be at least 10', 16, 10);
    END;
    tnx a lot for ur help
    could u please explain what does select 1 mean ?
    and also what does if exists does?tnx

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Books Online is your friend and is only a keystroke away in Management Studio. Take a look at it if you haven't already

    EXISTS (Transact-SQL)

    "1" is just a dummy value in the SELECT. EXISTS validates whether the subquery returns at least one row but doesn't care what values are returned in those row(s).

Posting Permissions

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