Re: Trigger is not working when I test

From: Mike John (Mike.John_at_knowledgepool.com)
Date: 10/11/04 

Date: Mon, 11 Oct 2004 20:55:23 +0100

Some more details on "not working" would help. I also can't help wonderining
why on earth you are using a trigger for something that could be very simply
(and more efficiently ) done using a constraint. have a look at CHECK
CONSTRAINTS in BOL.

Mike John

"DCSLICK" <DCSLICK@discussions.microsoft.com> wrote in message
news:754AF358-77CC-4107-B8BC-CD594F0D5B2E@microsoft.com...
> Thank you for the info, that is the way I had it before but it never
> worked
> so I tried changing it around. So I put it back that way and when I test
> it
> is still not working.
> Can I use updated instead of inserted? Would that make a difference?
>
> Thanks again!!!!
>
> "Alejandro Mesa" wrote:
>
>> This is from BOL.
>>
>> deleted and inserted are logical (conceptual) tables. They are
>> structurally
>> similar to the table on which the trigger is defined, that is, the table
>> on
>> which the user action is attempted, and hold the old values or new values
>> of
>> the rows that may be changed by the user action. For example, to retrieve
>> all
>> values in the deleted table, use:
>>
>> SELECT *
>> FROM deleted
>>
>> **************************
>>
>> In your case, the table inserted has same structure as table
>> video_collection.
>>
>> CREATE TRIGGER CheckStock ON [video_collection]
>> FOR UPDATE
>> AS
>> IF EXISTS (SELECT * FROM inserted WHERE stock_on_hand < 0)
>> BEGIN
>> RAISERROR('Can not oversell products.', 16, 1)
>> RAISERROR('Transaction has been canceled.', 16, 1)
>> ROLLBACK
>> END
>>
>>
>>
>> AMB
>>
>>
>> "DCSLICK" wrote:
>>
>> > I was told that I need to add a business rule as one of the triggers
>> > that I
>> > am creating.
>> > In my program I have to write a trigger for
>> > insert, update on a database. For example If you see
>> > studentAge.inserted is <= 20 then rollback the insert
>> > with a error message else you can commit the insert or
>> > update.
>> >
>> > So for my trigger I created the following for my db.
>> >
>> > CREATE TRIGGER CheckStock ON [video_collection]
>> > FOR UPDATE
>> > AS
>> > IF EXISTS (SELECT * FROM stock_on_hand.inserted WHERE stock_on_hand <
>> > 0)
>> > BEGIN
>> > RAISERROR('Can not oversell products.', 16, 1)
>> > RAISERROR('Transaction has been canceled.', 16, 1)
>> > ROLLBACK
>> > END
>> >
>> > When testing I get an error message "invalid object name
>> > 'stock_on_hand.inserted'
>> >
>> >
>> > What am I doing wrong??
>> >
>> > TIA
>> >

Relevant Pages