Must declare the variable '@OldID'.

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)

Must declare the variable '@OldID'.

Author

Topic

Ágúst
Starting Member

7 Posts

Posted - 2004-06-04 : 07:11:29

Hi

I user the following sql script in a "instead of delete" trigger:
--
DECLARE @OldId int, @Email varchar(50), @Name varchar(50)

SELECT @OldId = AutoID, @Email = Email, @Name = [Name] FROM Deleted

if @OldID is not null
begin
Insert into DelPerson (OldId, Email, [Name])
Values ( @OldId, @Email, @Name)

DELETE FROM PERSON where autoid = @OldID
end
--

I have been using this code on ca.10-15 sql servers without any problems. Yesterday I was installing my application for a new customer and then suddenly this code throw a error:
Server: Msg 137, Level 15, State 2, Procedure triggerPersonDeleted, Line 30
Must declare the variable '@OldID'.
Server: Msg 137, Level 15, State 1, Procedure triggerPersonDeleted, Line 40
Must declare the variable '@OldID'.

I have found some topics on this forum for the error "Must declare the variable..." but yet I haven't been able to connect those to my error. Any hints warmly welcomed...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-04 : 07:18:28

Sounds like this database is case sensitive
You declare @OldId and use @OldID.

Also this trigger only deals with single row deletes - is that a good idea

==========================================
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.

srinivasanr
Starting Member

15 Posts

Posted - 2004-06-04 : 07:21:07

Hi,
Can you post the full trigger script (CREATE TRIGGER ... etc.,).
Let's walk around that..

Wallops !!

Ágúst
Starting Member

7 Posts

Posted - 2004-06-04 : 09:58:00

Hi and thanks for responding

The problem was (95% sure) that the database is case sensitive. After updating my trigger like this:
--
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--Skráð 31.10.2003
--Tilgangur: Setja þá starfsmenn sem er eytt í DelPerson töfluna,
-- clientar geta svo pollað þá töflu til að sjá hverjum hvefur verið eytt út.
--Höf.: Ágúst Valgarð Ólafsson agusto@vks.is
--breytti 29.12.2003 if @OldID is not null...
ALTER TRIGGER [triggerPersonDeleted] ON dbo.Person
INSTEAD OF DELETE
AS

Declare @n_OldId int
Declare @Email varchar(50)
Declare @Name varchar(50)

SELECT @n_OldId = AutoID, @Email = Email, @Name = [Name] FROM Deleted

if @n_OldId is not null
begin
Insert into DelPerson (OldID, Email, [Name])
Values ( @n_OldId, @Email, @Name)

DELETE FROM PERSON where autoid = @n_OldId
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--
... everything was ok.

Regarding your comment, nr, I have always thought the this trigger would be called FOR EACH deleted row, is that not true?

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-04 : 10:09:58

No. What Nigel was pointing out is that a DELETE trigger fires once for every delete, and a DELETE may affect multiple rows. You should re-write your query to assume multiple rows in the DELETED table.

Raymond

Ágúst
Starting Member

7 Posts

Posted - 2004-06-04 : 10:14:08

Thanks alot, I'll do that!

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-04 : 11:18:30


INSERT INTO DelPerson(OldID, Email, [Name]) 
SELECT OldId, Email, [Name] FROM deleted

DELETE FROM PERSON p INNER JOIN deleted d where p.autoid = d.autoid

Brett

8-)

Ágúst
Starting Member

7 Posts

Posted - 2004-06-04 : 13:55:41

Yes, probably the above suggestion runs more smoothly then:
--

insert into DelPerson (OldID, Email, [Name])
select AutoID, Email, [Name]
FROM Deleted
WHERE AutoID is not null

DELETE
FROM PERSON
WHERE AutoID in
(SELECT AutoID FROM DELETED WHERE AutoID is not null)

--

Or is it the same? That is, considering performance. Not that it matters in this case as this is a rather smalle database, I'm just wondering if there is a diffrence in performance using my 'in' syntax or the 'join' syntax from X002548 :-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-04 : 14:10:20

What version of SQL Server are you running?

I'm assuming you're doing this in Query Analyzer?

Do you know what Show Execution Plan is?

Brett

8-)

Ágúst
Starting Member

7 Posts

Posted - 2004-06-07 : 09:28:43

See picture: http://agust.org/Images/untitled.jpg

it is the same performance using in it seems...

Ágúst
Starting Member

7 Posts

Posted - 2004-06-07 : 09:29:37

btw i'm using sql server 2000

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-07 : 10:53:17

Worthwhile testing but unless there is a very good reason not to I would go with

insert into DelPerson (OldID, Email, [Name])
select AutoID, Email, [Name]
FROM Deleted
WHERE AutoID is not null

DELETE PERSON p
INNER JOIN deleted d
on p.autoid = d.autoid
WHERE d.AutoID is not null

==========================================
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.

Ágúst
Starting Member

7 Posts

Posted - 2004-06-08 : 06:23:03

Thanks for the input

DELETE PERSON p
INNER JOIN deleted d
on p.autoid = d.autoid
WHERE d.AutoID is not null

--results in:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'p'.

--ok is:
DELETE PERSON
FROM PERSON p
INNER JOIN deleted d
on p.autoid = d.autoid
WHERE d.AutoID is not null

... just a syntax twist...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources