Help for Trigger

Hello

I would like to get the content of a field based in the field Name.
Suppose a table with a field Named 'LastName' for wich there is a trigger
after update
I store the field name in a local variable

Set @.ColName = 'LastName'

How can I retrieve the value of the @.ColName from the inserted table using
the @.Colname variable ?

I tried this:

Set @.Cmd = 'DECLARE @.DataValue varchar(100) Set @.DataValue = (Select i.' +
@.ColName + ' from inserted i) print @.DataValue'
exec (@.Cmd)

print @.Cmd gives

DECLARE @.DataValue varchar(100) Set @.DataValue = (Select i.LastName from
inserted i) print @.DataValue

But I got the following error

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'inserted'.

Any idea why ?

Thanks for your help

ThierryOn Fri, 23 Apr 2004 20:57:10 +0200, Thierry Marneffe wrote:

>Hello
>I would like to get the content of a field based in the field Name.
>Suppose a table with a field Named 'LastName' for wich there is a trigger
>after update
>I store the field name in a local variable
>Set @.ColName = 'LastName'
>How can I retrieve the value of the @.ColName from the inserted table using
>the @.Colname variable ?
>I tried this:
>Set @.Cmd = 'DECLARE @.DataValue varchar(100) Set @.DataValue = (Select i.' +
>@.ColName + ' from inserted i) print @.DataValue'
>exec (@.Cmd)
>print @.Cmd gives
>DECLARE @.DataValue varchar(100) Set @.DataValue = (Select i.LastName from
>inserted i) print @.DataValue
>But I got the following error
>Server: Msg 208, Level 16, State 1, Line 1
>Invalid object name 'inserted'.
>Any idea why ?
>Thanks for your help
>Thierry

Hi Thierry,

The inserted and deleted pseudo-tables can only be used in the
trigger. Invoking dynamic SQL creates a new environment, so you can't
use the inserted and deleted tables there.

If you don't use dynamic SQL, all will be swell:

CREATE TRIGGER TestIt
ON MyTable AFTER UPDATE
AS
DECLARE @.DataValue varchar(100)
SET @.DataValue =
(SELECT LastName
FROM inserted)
PRINT @.DataValue
go

Of course, this trigger will still result in an error if you perform
an update that affects more than one row - always remember that a
trigger is fired exactly once for each update statement, regardless of
the number of rows that match the search criteria (can be anything
from 0 up to the complete table) and regardless of whether the data
was actually chaged or not (ie UPDATE MyTable SET MyColumn = MyColumn
will fire the trigger and will have the complete table in the inserted
ande deleted pseudo-tables).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Help Cannot resolve collation conflict for equal to operation.

Hello,
I am trying to setup replication for a database and I get the following
error. This is the content database of WSS. I noticed that the collation was
set for: Latin1_General_CI_AS_KS_WS which is diffent from our normal
collation setting SQL_Latin1_General_CP1_CI_AS, however I created the
rplDatabase with the same collation. Any help would be greatly appreciated.
Jake
Error Message:
The schema script '\\SERVER\D$\Program Files\Microsoft SQL
Server\MSSQL$DBSERVER\ReplData\unc\SERVER$DBSERVER _STS_WCPC-L-02_Content_STS
_WCPC_L_02_Content\20040416221336\proc_FindDocs_16 .sch' could not be
propagated to the subscriber.
Error Details:
The schema script '\\SERVER\D$\Program Files\Microsoft SQL
Server\MSSQL$DBSERVER\ReplData\unc\SERVER$DBSERVER _STS_WCPC-L-02_Content_STS
_WCPC_L_02_Content\20040416221336\proc_FindDocs_16 .sch' could not be
propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Cannot resolve collation conflict for equal to operation.
(Source: SERVER\DBSERVER (Data source); Error number: 446)
are you replicating to a SQL 7 database?
If you so you to configure your publication for this in the Specify
Subscriber Types dialog box of the create publication wizard. Otherwise in
the specify articles dialog of the same wizard click on the browse button to
the right of the articles and in the snpashot tab, select the collation tab.
"Jake" <rondican@.hotmail.com> wrote in message
news:%23RV1LCEJEHA.2380@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to setup replication for a database and I get the
following
> error. This is the content database of WSS. I noticed that the collation
was
> set for: Latin1_General_CI_AS_KS_WS which is diffent from our normal
> collation setting SQL_Latin1_General_CP1_CI_AS, however I created the
> rplDatabase with the same collation. Any help would be greatly
appreciated.
> Jake
> Error Message:
> The schema script '\\SERVER\D$\Program Files\Microsoft SQL
>
Server\MSSQL$DBSERVER\ReplData\unc\SERVER$DBSERVER _STS_WCPC-L-02_Content_STS
> _WCPC_L_02_Content\20040416221336\proc_FindDocs_16 .sch' could not be
> propagated to the subscriber.
> Error Details:
> The schema script '\\SERVER\D$\Program Files\Microsoft SQL
>
Server\MSSQL$DBSERVER\ReplData\unc\SERVER$DBSERVER _STS_WCPC-L-02_Content_STS
> _WCPC_L_02_Content\20040416221336\proc_FindDocs_16 .sch' could not be
> propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ----
--
> --
> Cannot resolve collation conflict for equal to operation.
> (Source: SERVER\DBSERVER (Data source); Error number: 446)
> ----
--
> --
>
|||Jake,
Am I right to conclude that you have included the stored procedure
[proc_FindDocs.....] as an article in your publication? If yes, then the
error is occurring while applying the stored procedure to the subscriber,
because it contains code that is comparing two values (e.g. columns or
variables) of different data types. You could try to apply the stored
procedure script manually (through Query Analyser) to the subscriber
database to see if you get the same error. If yes, then analyse the stored
procedure to see which columns are involved and check their collation. This
should give you an indication of where the collation problem is occurring.
Hope the above helps.
Raj Moloye