Triggers - 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 2008 Forums

Transact-SQL (2008)

Triggers

Author

Topic

Lukesb
Starting Member

6 Posts

Posted - 2009-03-09 : 10:59:39

Hi Everyone

Hope I got this posted in right section...

I am trying to create a trigger in SQL server management studio...

I have used the following

create trigger EditDesc
on dbo.ILines
after insert
as
begin
if (select PG
from inserted) =20

update inserted
set BopDes = 'TESTESTESTESTEST'
end
go

I can't run it to create the trigger as table inserted doesn't exist...

How can I get this saved so that it runs when records are inserted on the table dbo.ILines..?

Thanks very much

Luke

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-09 : 11:09:08

You can't update the virtual tables [inserted] and [deleted]. If you want to overwrite the new values you need to update the target table [ILines] - INNER JOIN to [inserted] so that you only update the new rows.

Be One with the Optimizer
TG

Lukesb
Starting Member

6 Posts

Posted - 2009-03-09 : 11:20:57

Hi TG

Thanks for that. It makes perfect sense now you've explained it...

I will keep on at it now...

If I get it right, do I just execute to save it..?

Cheers

Luke

Lukesb
Starting Member

6 Posts

Posted - 2009-03-09 : 11:43:30

I now have the following

create trigger EditDesc
on dbo.ILines
for insert
as

if (select PG
from inserted) =20
begin

update dbo.ILines
set BopDes = 'TESTESTESTESTEST'
where dbo.ILines.[DateTime] = (select datetime from inserted)
end
go

But get the following error

Msg 8197, Level 16, State 4, Procedure EditDesc, Line 1
The object 'dbo.ILines' does not exist or is invalid for this operation.

Any ideas on this..?

Thanks

Luke

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-09 : 12:13:46

[code]

Create Trigger EditDesc
on dbo.ILines
for insert
as

IF Exists (Select 1
from inserted
Where PG = 20)
begin

update IL
set IL.BopDes = 'TESTESTESTESTEST'
from dbo.ILines IL Inner join
Inserted I
ON I.[datetime] = IL.[datetime]

end
go[/code]

Lukesb
Starting Member

6 Posts

Posted - 2009-03-09 : 12:26:13

Hey sodeep

Thanks for reply, but I am still getting an error. This time it says

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.ILines'.

Thanks again..!

Luke

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-09 : 14:03:12

Do you have table named dbo.ILines?

Lukesb
Starting Member

6 Posts

Posted - 2009-03-09 : 15:05:46

Yes, I do...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-09 : 15:36:43

The trigger code is fine. Do you get that error when you insert into your ilines table? It is probably a problem with your insert statement. Or your call to the SP that does the insert.

Be One with the Optimizer
TG

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-10 : 05:52:40

quote:
Originally posted by sodeep

Do you have table named dbo.ILines?

are you sure it's a table this seems to be a view.
select * from sys.objects
where name like '%ILines'

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources