Fun of INSTEAD OF UPDATE trigger

29 Jul 2005 7:03 PM

James Ma

Hi All,

Here I have a view like this:
create view vw_Lcustkeycode as
    select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
    union all
    select *, convert(bit,1) as IsArchived from
DataEntryArchive.dbo.aLcustkeycode

I created an INSTEAD OF UPDATE trigger like:
CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
AS
BEGIN
    if update(UserName) and update(DateModified) begin
        update d
        set d.UserName=i.UserName, d.DateModified=i.DateModified
        from dbo.Lcustkeycode d
            inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
        where i.IsArchived=0

        update d
        set d.UserName=i.UserName, d.DateModified=i.DateModified
        from DataEntryArchive.dbo.aLcustkeycode d
            inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
        where i.IsArchived=1
    end
END

When I run following update in Query Analyzer (notice the lcustkeycode_id is
primary key so only 1 row should be affected):
update vw_LCustKeycode
set username='jamma', datemodified=getdate()
where lcustkeycode_id=111060167

It said:
(2 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)

I check the data and they are correct and really only 1 row was updated, but
why it said so many rows were affected? I find there are no other trigers
sitting there except my instead of trigger.

Can anyone here explain this strange behaviour?

Thanks,
James

29 Jul 2005 7:15 PM

David Gugick

James Ma wrote:
Show quote

> Hi All,
>
> Here I have a view like this:
> create view vw_Lcustkeycode as
> select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
> union all
> select *, convert(bit,1) as IsArchived from
> DataEntryArchive.dbo.aLcustkeycode
>
> I created an INSTEAD OF UPDATE trigger like:
> CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
> AS
> BEGIN
> if update(UserName) and update(DateModified) begin
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from dbo.Lcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=0
>
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from DataEntryArchive.dbo.aLcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=1
> end
> END
>
> When I run following update in Query Analyzer (notice the
> lcustkeycode_id is primary key so only 1 row should be affected):
> update vw_LCustKeycode
> set username='jamma', datemodified=getdate()
> where lcustkeycode_id=111060167
>
> It said:
> (2 row(s) affected)
> (1 row(s) affected)
> (8 row(s) affected)
> (0 row(s) affected)
> (8 row(s) affected)
>
> I check the data and they are correct and really only 1 row was
> updated, but why it said so many rows were affected? I find there are
> no other trigers sitting there except my instead of trigger.
>
> Can anyone here explain this strange behaviour?
>
> Thanks,
> James

Look in Profiler and see what it's doing (look at SP:StmtCompleted
events in addition to SQL:StmtCompleted).

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

29 Jul 2005 7:37 PM

James Ma

Thanks for your quick reply. Just now I closed Query Analyzer and entered it
again, then the results become:
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)

Seems fine now. I can't explain what happened just now. Even when I set
nocount off, it retuned.
(2 row(s) affected)
(8 row(s) affected)

But now everything is fine.

Show quote

"David Gugick" wrote:

> James Ma wrote:
> > Hi All,
> >
> > Here I have a view like this:
> > create view vw_Lcustkeycode as
> > select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
> > union all
> > select *, convert(bit,1) as IsArchived from
> > DataEntryArchive.dbo.aLcustkeycode
> >
> > I created an INSTEAD OF UPDATE trigger like:
> > CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
> > AS
> > BEGIN
> > if update(UserName) and update(DateModified) begin
> > update d
> > set d.UserName=i.UserName, d.DateModified=i.DateModified
> > from dbo.Lcustkeycode d
> > inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> > where i.IsArchived=0
> >
> > update d
> > set d.UserName=i.UserName, d.DateModified=i.DateModified
> > from DataEntryArchive.dbo.aLcustkeycode d
> > inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> > where i.IsArchived=1
> > end
> > END
> >
> > When I run following update in Query Analyzer (notice the
> > lcustkeycode_id is primary key so only 1 row should be affected):
> > update vw_LCustKeycode
> > set username='jamma', datemodified=getdate()
> > where lcustkeycode_id=111060167
> >
> > It said:
> > (2 row(s) affected)
> > (1 row(s) affected)
> > (8 row(s) affected)
> > (0 row(s) affected)
> > (8 row(s) affected)
> >
> > I check the data and they are correct and really only 1 row was
> > updated, but why it said so many rows were affected? I find there are
> > no other trigers sitting there except my instead of trigger.
> >
> > Can anyone here explain this strange behaviour?
> >
> > Thanks,
> > James
>
> Look in Profiler and see what it's doing (look at SP:StmtCompleted
> events in addition to SQL:StmtCompleted).
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
>

1 Aug 2005 6:30 AM

Razvan Socol

Hello, James

The extra "n row(s) affected" are probably a side effect of the "Show
execution plan" in Query Analyzer. Retry your query with this option on
and off to see if that's the problem.

Razvan