Fun of INSTEAD OF UPDATE trigger

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,
JamesJames 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|||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.
"David Gugick" wrote:

> James Ma wrote:
> 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
>|||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

Fully qualified name of a table

Hi all,
How can I find the fully qualified name of a table, if all I have is the
table name
I realise that this might be a bit ambigious, but I was wondering.
For example, if I had a table name like employees. It could be in northwind,
or in corporate or both
is there a way to find this out
Thanks
RobertNo easy way, since the object names are not contained in one single table. T
he object names are
contained in a system table or a view inside each database. But you could cr
eate a stored procedure
in which you use a cursor to loop each database and construct dynamic SQL to
pass such a query
against that system table or view. Or you could cheat and use an undocumente
d stored procedure that
dopes just that:
EXEC sp_MSForeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES WHERE TABLE_N
AME = ''authors'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bravery" <me@.u.com> wrote in message news:OmHh35UmGHA.1404@.TK2MSFTNGP05.phx.gbl...[
color=darkred]
> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>[/color]|||> How can I find the fully qualified name of a table, if all I have is the
> table name
If all you have is a table name, then you could get 1 or 20 answers (even
within the same database, because you could have dbo.Employees and
someOtherUser.Employees).
Now you are talking about going across databases, that's even more potential
answers. Here is how to find all the tables named 'Employees' in the
databases Northwind and Corporate:
SELECT '[' + TABLE_CATALOG + '].[ + TABLE_SCHEMA + '].[' + TABLE_NAME
FROM NorthWind.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Employees'
AND TABLE_TYPE = 'BASE_TABLE'
UNION
SELECT '[' + TABLE_CATALOG + '].[ + TABLE_SCHEMA + '].[' + TABLE_NAME
FROM Corporate.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Employees'
AND TABLE_TYPE = 'BASE_TABLE';|||You might try something like:
sp_msforeachdb 'select ''?.'' + rtrim(table_schema) + ''.'' +
rtrim(table_name)
from ?.information_schema.tables where table_name = ''employees'''
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Robert Bravery" wrote:

> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>
>|||Try,
create table #t (
tc sysname,
ts sysname,
tn sysname
)
declare @.sql nvarchar(4000)
declare @.tc sysname
declare my_cursor cursor
local
fast_forward
for
select [name]
from master.dbo.sysdatabases
where [name] not in ('master', 'tempdb', 'msdb', 'model')
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tc
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'use [' + @.tc + ']; select TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select
*
from
#t as a
where
exists (
select
*
from
#t as b
where
b.ts = a.ts
and b.tn = a.tn
and b.tc != a.tc
)
order by
tn,
tc,
ts
drop table #t
go
AMB
"Robert Bravery" wrote:

> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>
>