Seriously confused - 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)

Seriously confused

Author

Topic

Dumps
Starting Member

10 Posts

Posted - 2006-01-26 : 18:20:57

Go easy on me people, I'm a new to this!

I have the following Delete trigger;

CREATE TRIGGER dbo.MT_ProjType_CheckProjects ON ProjectTypes
FOR DELETE
AS

SET NOCOUNT ON

--get id of deleted project type from DELETED
DECLARE @DeletedID int
SET @DeletedID = (SELECT projecttypeid FROM DELETED)

--see if any projects are of the deleted type
DECLARE @CountType int --number of projects of deleted type

SELECT @CountType = count(projecttype)
FROM project
WHERE projecttype = @DeletedID

--if 0 then no projects of deleted type so return
IF @CountType = 0
BEGIN
RETURN
END

ELSE
BEGIN

--see if Unknown already exists as an option in projecttypes
DECLARE @UnknownExists int --not 0 if unknown doesn't exist

SELECT @UnknownExists = count(projecttype)
FROM projecttypes
WHERE projecttype = 'Unknown'
END

--if 0 then insert Unknown into projecttypes and give all projects
--of deleted type Unknown's corresponding id
IF @UnknownExists = 0
BEGIN
--insert Unknown and get relevant id
DECLARE @IDofUnknown int

INSERT INTO projecttypes (projecttype)
VALUES ('Unknown')

--get ID of unknown
SET @IDofUnknown = SCOPE_IDENTITY()

--PROBLEM
--update all projects of old type with new Unknown ID
UPDATE project
SET projecttype = @IDofUnknown
WHERE projecttype = @DeletedID
END
--PROBLEM

ELSE
BEGIN
--get id of Unknown from projecttypes
DECLARE @UnknownID int

SELECT @UnknownID = projecttypeid
FROM projecttypes
WHERE projecttype = 'Unknown'

--PROBLEM
--update all projects of old type with UnknownID
UPDATE project
SET projecttype = @UnknownID
WHERE projecttype = @DeletedID
END
--PROBLEM

SET NOCOUNT OFF
GO

Essentially, I'm trying to update all existing rows in table Project where the project type is the same as that being deleted from the table Project types. So if the project type has been used for another project, it is replaced with Unknown (which is inserted if it doesn't already exist in the Project type table).

I understand that @DeletedID has to be one value and having checked what is returned it seems to be correct - ie. @DeletedID is the ID of the project type deleted. The trigger works fine when only one existing project is being updated but not for multiple updates.

As far as I can tell I'm only using one value for @DeletedID so the update should work fine - shouldn't it????

Please help!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-26 : 18:40:40

You have a serious design error in this trigger. A trigger has to be able to handle multiple rows, and you have coded it with the assumption that only one row at a time will be updated.

This will generate an error when the deleted table contains more than one row.

SET @DeletedID = (SELECT projecttypeid FROM DELETED)

Besides this problem, I don't understand why you are doing this. Why allow the deletion of a project type when it is being referenced? It would be better to use a foreign key reference to keep this from happening. Then, if you want to do this, create a stored procedure to update the referencing rows before deleting a project type.

CODO ERGO SUM

Dumps
Starting Member

10 Posts

Posted - 2006-01-27 : 14:02:07

Thanks Michael, and I see what you mean. I think I'll have another go at this and do as you suggest.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources