accessing fake data inside an INSTEAD OF trigger

2 Mar 2006 11:34 PM

steven

Hello,

Is it possible to access inserted data through an UPDATE INSTEAD OF
trigger if the condition of the query does not match any row in a
table?

For example :

UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'

If there is no row inside myTable matching the condition "WHERE
myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
error but the inserted table remains empty.

Is there a way to access the value 'abc' in such a situation?

thank you
s

2 Mar 2006 11:53 PM

Roy Harvey

No, you do not have access to the originating command, nor any
parameters of that command. What you have access to are the DELETED
and INSERTED tables, and the table that was updated.

Roy Harvey
Beacon Falls, CT

Show quote

On 2 Mar 2006 15:34:44 -0800, "steven" <stevensh***@gmail.com> wrote:

>Hello,
>
>Is it possible to access inserted data through an UPDATE INSTEAD OF
>trigger if the condition of the query does not match any row in a
>table?
>
>For example :
>
>UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
>
>If there is no row inside myTable matching the condition "WHERE
>myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
>error but the inserted table remains empty.
>
>Is there a way to access the value 'abc' in such a situation?
>
>thank you
>s

3 Mar 2006 3:30 PM

steven

OK ... but this data should exist somewhere.

I mean if the server returns an empty cursor when I do a SELECT * FROM
INSERTED within the trigger, it is because it has access to myColumn2 =
'xyz'. Any API to access the query content, even if it does not match
anything in the table?

thank you

Roy Harvey wrote:
Show quote

> No, you do not have access to the originating command, nor any
> parameters of that command. What you have access to are the DELETED
> and INSERTED tables, and the table that was updated.
>
> Roy Harvey
> Beacon Falls, CT
>
>
> On 2 Mar 2006 15:34:44 -0800, "steven" <stevensh***@gmail.com> wrote:
>
> >Hello,
> >
> >Is it possible to access inserted data through an UPDATE INSTEAD OF
> >trigger if the condition of the query does not match any row in a
> >table?
> >
> >For example :
> >
> >UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
> >
> >If there is no row inside myTable matching the condition "WHERE
> >myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
> >error but the inserted table remains empty.
> >
> >Is there a way to access the value 'abc' in such a situation?
> >
> >thank you
> >s

3 Mar 2006 11:57 PM

Roy Harvey

On 3 Mar 2006 07:30:03 -0800, "steven" <stevensh***@gmail.com> wrote:

>OK ... but this data should exist somewhere.
>
>I mean if the server returns an empty cursor when I do a SELECT * FROM
>INSERTED within the trigger, it is because it has access to myColumn2 =
>'xyz'. Any API to access the query content, even if it does not match
>anything in the table?
>
>thank you

Nothing like that I've ever heard of. Or imagined, for that matter.

Roy Harvey
Beacon Falls, CT