sp updates not persisting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp updates not persisting

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-21 : 14:16:49

I'm calling a sp from an instead of trigger. The sp udpates some records and the row count after the updates is saying that 2 records have been updated.

Only problem is that there's no sign of the updates when the trigger has completed. The rows still have the same values.

If I make the trigger an after trigger then all is fine. So how do I get these updates to persist when they're made within the context of an instead of trigger?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-21 : 15:24:45

<stupidQuestion>
Do your updates include updating the table that has the instead of trigger?
</stupidQuestion>

If you update a table with an instead of update trigger, but the trigger has no code, your table will not be updated.

Be One with the Optimizer
TG

EDIT:
example of what I mean:


set nocount on
use pubs
go
create table junk (i int primary key, c varchar(15))
go
create trigger tr_junk_upd on junk instead of update 
as

print 'update someother table'

/* --don't update junk
update a set 
	a.c = i.c
from	junk a
join	inserted i
		on i.i = a.i
*/
go

insert junk (i,c) values (1,'tg')
go

select * from junk
update junk set c = 'x-factor' where i = 1
select * from junk

go
drop table junk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources