RE: Trigger is not working when I test

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 10/11/04 

Date: Mon, 11 Oct 2004 12:23:16 -0700

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

  • Re: Trigger is not working when I test
    ... I also can't help wonderining ... why on earth you are using a trigger for something that could be very simply ... CONSTRAINTS in BOL. ... >> the rows that may be changed by the user action. ...
    (microsoft.public.sqlserver.programming)
  • Re: NEED TO CALL AS400 S.P
    ... That is to use a trigger on a table and perform an INSERT/UPDATE which will execute the trigger. ... What an error message! ... > what your OLE DB provider is feed the AS400 with? ... > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se ...
    (microsoft.public.data.oledb)
  • Re: using a trigger to enforce referential integrity
    ... The first thing to do is COMPLETELY and EXACTLY specify the error message ... Lets avoid "something like..." ... you can then move on to rewriting your trigger to ... FROM tblItemRelationship AS IR ...
    (microsoft.public.sqlserver.programming)
  • Re: Write conflict error after adding trigger to table
    ... This error message is usually the result of a missing primary key or a ... VIEW_METADATA and create an appropriate InsteadOf trigger to have this view ... > view in that database. ... > of using the stored proc as a datasource. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Killed by Triggers
    ... When I removed the trigger the error disappeared. ... The second situation is when a detail record was attempted, after the header ... If you don't get an error message, ... UPDATE TABLE01 SET CREATEDBY = CURRENT_USER, ...
    (microsoft.public.sqlserver.security)
Loading