updating #temp table - 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

General SQL Server Forums

New to SQL Server Programming

updating #temp table

Author

Topic

Msandlana
Starting Member

33 Posts

Posted - 2008-01-11 : 06:22:41

hi All

I have these following table Faculty and #tempFaculty and I want to save chages that I made in Faculty also to #tempFaculty(which is temporal)
I tried this but Its not working

alter trigger [trifaculty]
on [dbo].[faculty]
after update
as
if exists (select * from inserted)

begin
update h
set h.faculty = f.faculty
from #tempfaculty h
inner join faculty f
on f.facultyId = h.facultyId
inner join inserted i
on f.facultyId = i.facultyid
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 06:37:07

Try:-

alter trigger [trifaculty]
on [dbo].[faculty]
after update
as
if exists (select * from inserted)

begin
update h
set h.faculty = i.faculty
from #tempfaculty h 
inner join inserted i
on i.facultyId = h.facultyid
end

Msandlana
Starting Member

33 Posts

Posted - 2008-01-11 : 08:15:35

It did not work I tried that one its show the error message that say "no row was updated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 08:28:12

Doesnt your temp table has records in faculty which were updated? Can you give example data from temp table & faculty and give expected o/p?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-11 : 09:24:34

How do you create #tempFaculty
How do you run the update that fires the trigger

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Msandlana
Starting Member

33 Posts

Posted - 2008-01-14 : 06:38:18

Hello NR
This is how I create the temporal table(#tempFaculty)

Create table #tempFaculty
(
facultyId int primary key,
faculty varchar(50)
)
insert into #temFaculty (facultyId, faculty)
select facultyId, faculty from faculty where 1 = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 07:10:54

Are you doing the update operation from same connection?

Msandlana
Starting Member

33 Posts

Posted - 2008-01-16 : 02:51:41

Yes visakh16

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-17 : 06:20:05

That won't fire the trigger as you are insetring into #tempFaculty.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Msandlana
Starting Member

33 Posts

Posted - 2008-01-18 : 08:01:23

So tell nr What should I do? what do you suggest that can help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 08:47:08

can you post what you original requirement was?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources