Update Trigger to modify two tables

Question

Update Trigger to modify two tables

bpmosullivan

Mr or Mrs. 500

Points: 538
More actions
September 25, 2018 at 1:43 pm

#364211

Hi folks,
I'm trying to update 2 tables (one audit table and the main table with the most recent username) with an After update Trigger but getting subquery returned more than 1 value errors in the application
Do i need to seperate and have two seperate after update triggers ?
thanks for assitance
ALTER TRIGGER [dbo].[InfoTableUpdateTrigger] ON [dbo].[InfoTable]
AFTER UPDATE

AS
DECLARE
@ID_COUNT_ VARCHAR(16),
@ActionDate smalldatetime,
BEGIN
SET @ActionDate = (SELECT GETDATE())
INSERT INTO dbo.InfoTable_AuditTable(ID_COUNT_, Type, OLDType)
SELECT TOP 1 i.ID_COUNT_, i.Type_, d.Type_
FROM Inserted i
INNER JOIN Deleted d ON i.ID_COUNT_ = d.ID_COUNT_
SET @ID_COUNT_ = (SELECT ID_COUNT_ FROM INSERTED)
UPDATE InfoTable SET LastUpdatedBy=SUSER_SNAME() WHERE ID_COUNT_ = @ID_COUNT_
UPDATE InfoTable SET TimeRecorded=@ActionDate WHERE ID_COUNT_ = @ID_COUNT_

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Triggers need to be able to handle any given number of rows. You can not rely on there always being just 1 row getting inserted. Any multi-row insert will result in the INSERTED virtual table having all of those rows in it, so you have to code with that in mind. And just to be sure, check that you don't have recursive triggers enabled.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 2

Thanks SSC

If i take out the Select Top 1, will this query handle multiple rows inserted or do i need to change my query to handle multiple rows ? There could be multiple rows inserted and that is ok
I also need the trigger to update a value in the main table
I need to lookup recursive

Any assistance appreciated as this i am a noob

sgmunson SSC Guru Points: 110640 More actions · Answer 3

bpmosullivan - Tuesday, September 25, 2018 2:17 PM
Thanks SSC
If i take out the Select Top 1, will this query handle multiple rows inserted or do i need to change my query to handle multiple rows ?
I also need the trigger to update a value in the main table
I need to lookup recursive
Any assistance appreciated as this i am a noob

Without knowing exactly what your trigger is required to do, I have no way to know how to fix it. It appears that you are inserting additional rows in the table that is being updated. That's somewhat unusual, so I would need the WHY and what so I can understand the objective. Please be very specific with the details...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 4

No problem, thank you

One Table is an AuditTable so i'm placing the old values and new values just for comparison in the AuditTable when records are changed
Then on the Infotable, i'm updating the time and the windows user fields (LastUpdatedBy , TimeRecorded) in the records that were modified to show who changed it as some people don't have access to audit table

I don't seem to get the error 'subquery returned more than 1 value errors in the application' if i omit the the second part of the trigger. Is this causing the issue do you think ?

SET @ID_COUNT_ = (SELECT ID_COUNT_ FROM INSERTED)
UPDATE InfoTable SET LastUpdatedBy=SUSER_SNAME() WHERE ID_COUNT_ = @ID_COUNT_
UPDATE InfoTable SET TimeRecorded=@ActionDate WHERE ID_COUNT_ = @ID_COUNT_

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

I think the your trigger should INSERT as many audit rows as there were rows affected by the original UPDATE, and you should likewise only have one UPDATE statement that handles all the rows. Plus, as already mentioned, make sure you don't have recursive triggers enabled.

This version should work no matter how many rows were updated.

UPDATE dbo.InfoTable SET LastUpdatedBy = SUSER_SNAME(), TimeRecorded = GETDATE() WHERE ID_COUNT_ IN (SELECT ID_COUNT_ FROM Inserted);

INSERT INTO dbo.InfoTable_AuditTable(ID_COUNT_, Type, OldType) SELECT i.ID_COUNT_, i.Type_, d.Type_ FROM Inserted i INNER JOIN Deleted d ON d.ID_COUNT_ = it.ID_COUNT_;

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 6

thank you

How do i check for recursive triggers enabled ? Is that a setting or check if other triggers exist on the database etc.

Joe Torre SSChampion Points: 10248 More actions · Answer 7

Does this work?

ALTER TRIGGER [dbo].[InfoTableUpdateTrigger] 
ON [dbo].[InfoTable] 
WITH SCHEMABINDING
FOR AFTER UPDATE
AS

INSERT INTO dbo.InfoTable_AuditTable ( ID_COUNT_
, TYPE
, OLDType)
SELECT i.ID_COUNT_
, i.Type_
, d.Type_
FROM Inserted i
INNER JOIN Deleted d
ON i.ID_COUNT_ = d.ID_COUNT_;
UPDATE it
SET LastUpdatedBy = SUSER_SNAME()
, TimeRecorded = GetDate()
FROM dbo.InfoTable it
JOIN inserted i
ON it.ID_COUNT_ = i.ID_COUNT_;
GO

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 8

thanks guys. i will try soon and let you know

A third party app can make multiple record updates to the table and that is where i am seeing the sql error of subquery returned more than 1 value errors in the application. i think i narrowed it down to my updating
LastUpdatedBy, TimeRecorded fields in the different table so hopefully your assistance will work

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 9

bpmosullivan

Mr or Mrs. 500

Points: 538

September 25, 2018 at 3:13 pm

#2007021

what does With SCHEMABINDING mean ?

Joe Torre SSChampion Points: 10248 More actions · Answer 10

It prevents DDL changes to the table without dropping the trigger

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 11

Thanks Joe. Does that mean it will complete the trigger and accept no data edits on the table until the trigger completes ?

Would that possibly cause an issue for other users updating ?

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 12

I tried the following for the trigger but a strange thing seems to be happening

UPDATE dbo.InfoTable SET LastUpdatedBy = SUSER_SNAME(), TimeRecorded = GETDATE()
WHERE ID_COUNT_ IN (SELECT ID_COUNT_ FROM Inserted);

INSERT INTO dbo.InfoTable_AuditTable(ID_COUNT_, Type, OldType)
SELECT i.ID_COUNT_, i.Type_, d.Type_
FROM Inserted i
INNER JOIN Deleted d ON d.ID_COUNT_ = i.ID_COUNT_;

It seems to be putting 2 rows in my Audit Table
One row that is the old record
Second row contains the old value and new value combined in the one row (Which is what i'm looking for)

Any ideas why it is also putting the old record into the Audit table also

Joe Torre SSChampion Points: 10248 More actions · Answer 13

It prevents schema changes to the table without dropping the trigger.

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 14

Thanks Joe, your trigger worked perfectly also but i can't seem to get it down to one record. I've researched and found that an update trigger is both an insert and delete so this is causing the issue

i have followed the below example but it still is putting in 2 records 🙁

https://stackoverflow.com/questions/4491989/sql-update-query-is-causing-two-rows-in-a-trigger