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

General SQL Server Forums

New to SQL Server Programming

triggers

Author

Topic

sharankruthi
Starting Member

22 Posts

Posted - 2009-07-21 : 03:42:13

ALTER Trigger updtrig
ON chennaiexp
FOR INSERT, UPDATE
AS
IF UPDATE(age) -- if age field is updated change the status
BEGIN
DECLARE @viewage tinyint
DECLARE @id int
DECLARE @seating tinyint
DECLARE @coach tinyint
DECLARE @tktstatus varchar(20)
DECLARE ages CURSOR FOR SELECT age,passengerid,seatloc,coach FROM inserted
OPEN ages
FETCH next FROM ages into @viewage, @id, @seating, @coach
WHILE(@@FETCH_STATUS = 0)
BEGIN
EXEC tktstatus @viewage,@tkt = @tktstatus OUTPUT -- Get status from a proc
UPDATE chennaiexp
SET status = @tktstatus where passengerid = @id -- update current row
EXEC chkseat @seating, @coach
FETCH next FROM ages into @viewage, @id, @seating, @coach
END
CLOSE ages
DEALLOCATE ages
END
IF update (status)
BEGIN
DECLARE @status varchar(20)
DECLARE @id1 varchar(20)
DECLARE a CURSOR FOR SELECT status FROM
OPEN a
FETCH next FROM a into @status
WHILE (@@FETCH_STATUS = 0)
BEGIN
if(@status = 'No ticket')
BEGIN
UPDATE chennaiexp
SET coach = NULL, seatloc = NULL
WHERE current of a
END
FETCH next from a into @status
END
END

I get following error.

Msg 16915, Level 16, State 1, Procedure updtrig, Line 30
A cursor with the name 'a' already exists.

what is the reason?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 07:05:47

[code]
IF update (status)
BEGIN
DECLARE @status varchar(20)
DECLARE @id1 varchar(20)
DECLARE a CURSOR FOR SELECT status FROM <table name>
OPEN a
FETCH next FROM a into @status
WHILE (@@FETCH_STATUS = 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sharankruthi
Starting Member

22 Posts

Posted - 2009-07-21 : 08:02:51

haha no i gave "inserted" for that. I missed out here

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 08:08:30

It's a really bad idea to implement a CURSOR in a TRIGGER. They are slow and you really, really want your triggers to be as fast as possible. You look like you are only updating something 1 row at a time in your cursor. This can probably be replaced with a set based updated with a CASE statement.

Maybe you should tell us what you want to accomplish. Also, what does your tktstatus stored proc do?

If it's simple then you should be replicating the logic here (or in a view) and then ditching both these cursors.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources