Prev: Sr Developer ,SFO
Next: How to put an "mdf" file into 2 different physical drive?
From: Markei54545 on 9 Aug 2010 05:54

Hi all,

I have created the following Trigger. It is a test as I have not had
to do this before. What I am trying to achieve is to trigger when a
record is inserted, and then to display a value from that inserted row
(in this case the document number). However, I get the error

"The multi-part identifier "SOPOrderReturn.DocumentNo" could not be
bound"

Any ideas? Any help gratefully received.

Mark


Create TRigger Test

On SOPOrderReturn

For Insert

as

DECLARE @DocNo nvarchar(20)

SET @DocNo = SOPOrderReturn.DocumentNo

RAISERROR ('Test Error Message', 16, 1)



From: Erland Sommarskog on 9 Aug 2010 07:46
Markei54545 (mark.blackall(a)gmail.com) writes:
> I have created the following Trigger. It is a test as I have not had
> to do this before. What I am trying to achieve is to trigger when a
> record is inserted, and then to display a value from that inserted row
> (in this case the document number). However, I get the error
>
> "The multi-part identifier "SOPOrderReturn.DocumentNo" could not be
> bound"
>
> Any ideas? Any help gratefully received.
>
> Mark
>
>
> Create TRigger Test
>
> On SOPOrderReturn
>
> For Insert
>
> as
>
> DECLARE @DocNo nvarchar(20)
>
> SET @DocNo = SOPOrderReturn.DocumentNo
>
> RAISERROR ('Test Error Message', 16, 1)

That's what you get for making up your syntax, rather than using Books
Online.

A trigger is no different from anything else, you cannot refer to column
values without a FROM clause. What is special in a trigger you have access
to two virtual tables: inserted and deleted. The first holds the inserted
rows, and in case of an UPDATE trigger, the afterimage of the updated rows.
The table deleted holds the rows by a DELETE statement and in an UPDATE
trigger, the before-image of the updated rows.

Note the use of plural in the previous parapgrah. A trigger fires once per
statement, and thus these table can hold mutiple rows, and reading a value
into variable is probably an error.

As for you want to do, triggers are intended for integrity checks and
cascading updates. While you can produce a result set from a trigger,
this is deprecated, and it may not work at all depending on a configuration
parameter.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Iain Sharp on 9 Aug 2010 07:52

1. The inserted trigger operates after all the inserts are done, so if
you insert more than one document, this will fail.

2. Use the 'special' table name 'inserted' to represent the new data.
See below

Iain


Create TRigger Test

On SOPOrderReturn

For Insert

as

DECLARE @DocNo nvarchar(20)

SET @DocNo = (select top 1 DocumentNo from inserted)

RAISERROR ('Test Error Message', 16, 1)


On Mon, 9 Aug 2010 02:54:44 -0700 (PDT), Markei54545
<mark.blackall(a)gmail.com> wrote:

>
>Hi all,
>
>I have created the following Trigger. It is a test as I have not had
>to do this before. What I am trying to achieve is to trigger when a
>record is inserted, and then to display a value from that inserted row
>(in this case the document number). However, I get the error
>
>"The multi-part identifier "SOPOrderReturn.DocumentNo" could not be
>bound"
>
>Any ideas? Any help gratefully received.
>
>Mark
>
>
>Create TRigger Test
>
>On SOPOrderReturn
>
>For Insert
>
>as
>
>DECLARE @DocNo nvarchar(20)
>
>SET @DocNo = SOPOrderReturn.DocumentNo
>
>RAISERROR ('Test Error Message', 16, 1)
>
>
From: Markei54545 on 9 Aug 2010 08:28
On 9 Aug, 12:52, Iain Sharp <ia...(a)pciltd.co.uk> wrote:
> 1. The inserted trigger operates after all the inserts are done, so if
> you insert more than one document, this will fail.
>
> 2. Use the 'special' table name 'inserted' to represent the new data.
> See below
>
> Iain
>
> Create TRigger Test
>
> On SOPOrderReturn
>
> For Insert
>
> as
>
> DECLARE @DocNo nvarchar(20)
>
> SET @DocNo = (select top 1 DocumentNo from inserted)
>
> RAISERROR ('Test Error Message', 16, 1)
>
> On Mon, 9 Aug 2010 02:54:44 -0700 (PDT), Markei54545
>
>
>
> <mark.black...(a)gmail.com> wrote:
>
> >Hi all,
>
> >I have created the following Trigger. It is a test as I have not had
> >to do this before. What I am trying to achieve is to trigger when a
> >record is inserted, and then to display a value from that inserted row
> >(in this case the document number). However, I get the error
>
> >"The multi-part identifier "SOPOrderReturn.DocumentNo" could not be
> >bound"
>
> >Any ideas? Any help gratefully received.
>
> >Mark
>
> >Create TRigger Test
>
> >On SOPOrderReturn
>
> >For Insert
>
> >as
>
> >DECLARE @DocNo nvarchar(20)
>
> >SET @DocNo = SOPOrderReturn.DocumentNo
>
> >RAISERROR ('Test Error Message', 16, 1)- Hide quoted text -
>
> - Show quoted text -

Thanks guys, that worked well.
 | 
Pages: 1
Prev: Sr Developer ,SFO
Next: How to put an "mdf" file into 2 different physical drive?