Problem with trigger for update

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)

Problem with trigger for update

Author

Topic

dejjan
Yak Posting Veteran

99 Posts

Posted - 2004-07-21 : 08:32:33

Hello, I have a problem with trigger for update. When I want to make multiple changes on table (10 rows), I get a message:

Server: Msg 512, Level 16, State 1, Procedure TR_PromenaAdresnihPodataka, Line 15
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Trigger:
ALTER TRIGGER TR_AddressHistory
ON ADDRESS
FOR UPDATE
AS
DECLARE @oldEMBG varchar(20), @newEMBG varchar(20),
@oldName varchar(20), @newName varchar(20),
@oldSurname varchar(25), @newSurname varchar(25),
@oldAddress varchar(40), @newAddress varchar(40),
@oldCity varchar(20), @newCity varchar(20)

SET @oldEMBG = (select embg from deleted )
SET @newEMBG = (select embg from inserted )
SET @oldName = (select name from deleted )
SET @newName = (select name from inserted )
SET @oldSurname = (select surname from deleted )
SET @newSurname = (select surname from inserted )
SET @oldAddress = (select address from deleted )
SET @newAddress = (select address from inserted )
SET @oldCity = (select city from deleted )
SET @newCity = (select city from inserted )

INSERT INTO AddressHistory
(oldEMBG, newEMBG, oldName, newName, oldSurname, newSurname, oldAddress, newAddress, oldCity, newCity, datum)
VALUES
(@oldEMBG, @newEMBG, @oldName, @newName, @oldSurname, @newSurname, @oldAddress, @newAddress, @oldCity, @newCity, getdate())

I can use cursor to make update, row by row, but is there some better way to do updates.

Thank for any helps

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 08:46:31

The Inserted and Deleted tables can have multiple rows if the UPDATE affects multiple rows.


ALTER TRIGGER TR_AddressHistory
ON ADDRESS
FOR UPDATE
AS
INSERT INTO AddressHistory
(oldEMBG, newEMBG)
SELECT d.embg,i.embg
FROM deleted d INNER JOIN inserted i on I.Key=D.Key

Note that in this example, I assume that you have a unique key on your Address table called Key. Basically, you need some way to correlate the entries in the INSERTED table to the corresponding entries in the DELETED table for this to work.

dejjan
Yak Posting Veteran

99 Posts

Posted - 2004-07-22 : 02:20:51

Problem is that I don't have a unique key in Address. There isn't a combination of columns wich could be unique. Someone who made application and this table didn't think about possibilities that I could need a history for this table. There are a lot of trash in this table and I want to fix it. It would be easy if I had a unique key.
Any new suggestion ?

Dejan Jankovic
[database administrator]

ALCO banka A.D.
Beograd
Pozeska 65b
11030 Beograd
tel: [+381][11] 30 50 383
mob: [+381][64] 11 022 48

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 03:36:28

Add an IDENTITY column to enforce a unique key? (and put a UNIQUE INDEX on it if it will be used in JOIN in the trigger

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-22 : 08:08:40

Then clean the data and add a unique constraint on whatever the natural key is so that doesn't get messed up again.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dejjan
Yak Posting Veteran

99 Posts

Posted - 2004-07-22 : 09:14:42

OK, but if I have this situation. Another, different trigger than this one, but very similar, with same problem. I will add few lines in trigger AddressHistory:

ALTER TRIGGER TR_AddressHistory
ON ADDRESS
FOR UPDATE
AS
DECLARE @oldEMBG varchar(20), @newEMBG varchar(20),
@oldName varchar(20), @newName varchar(20),
@oldSurname varchar(25), @newSurname varchar(25),
@oldAddress varchar(40), @newAddress varchar(40),
@oldCity varchar(20), @newCity varchar(20)

SET @oldEMBG = (select embg from deleted )
SET @newEMBG = (select embg from inserted )
SET @oldName = (select name from deleted )
SET @newName = (select name from inserted )
SET @oldSurname = (select surname from deleted )
SET @newSurname = (select surname from inserted )
SET @oldAddress = (select address from deleted )
SET @newAddress = (select address from inserted )
SET @oldCity = (select city from deleted )
SET @newCity = (select city from inserted )

if (@oldEMBG <> @newEMBG) or (@oldIme <> @newIme) or (@oldPrezime <> @newPrezime) or (@oldAdresa <> @newAdresa) or (@oldMesto <> @newMesto)

BEGIN

INSERT INTO AddressHistory
(oldEMBG, newEMBG, oldName, newName, oldSurname, newSurname, oldAddress, newAddress, oldCity, newCity, datum)
VALUES
(@oldEMBG, @newEMBG, @oldName, @newName, @oldSurname, @newSurname, @oldAddress, @newAddress, @oldCity, @newCity, getdate())

IF (@oldEMBG <> @newEMBG)
UPDATE IKART
SET EMBG = @newEMBG
WHERE EMBG = @oldEMBG
END

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 09:38:27

Doesn't look like your trigger is allowing for the fact that INSERTED and DELETED may contain multiple rows

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-22 : 09:42:55

By doing it right, you will only add a few lines if you update or insert a few lines which is kind of the idea I would think. You might want to disable the triggers, add the identity column, fix all your junk data, update the address table, then reenable the triggers. You need to get the junk fixed and then fix the triggers.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dejjan
Yak Posting Veteran

99 Posts

Posted - 2004-07-22 : 10:35:35

OK, thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources