469,950 Members | 2,094 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Error - subquery returning more than one value

1
Hie.

I have a trigger that monitors changes to my table fields but I get an error saying subquery returned more than one value.Below is the code for my trigger, hope you will figure out whats happening..

CREATE TRIGGER trgmyTableAuditFields ON myTable
WITH ENCRYPTION
FOR INSERT, UPDATE, DELETE
AS
BEGIN

DECLARE @col VARCHAR(200)
DECLARE @primarykeycol VARCHAR(200)
DECLARE @sql VARCHAR(2000)
DECLARE @action VARCHAR(200)
DECLARE @AuditInsert BIT
DECLARE @AuditUpdate BIT
DECLARE @AuditDelete BIT

SELECT @action =
CASE
WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'INSERTION'
WHEN (SELECT COUNT(*) FROM inserted) = 0 THEN 'DELETION'
ELSE 'UPDATE'
END

DECLARE curAuditFields CURSOR FOR
SELECT c.[name], ac.[AuditInsert], ac.[AuditUpdate], ac.[AuditDelete]
FROM syscolumns c
INNER JOIN sysobjects o ON o.[id] = c.[id]
INNER JOIN aucAuditColumns ac ON ac.[Column] = c.[name] AND ac.[Table] = o.[name]
WHERE o.[name] = 'myTable'
AND c.[xtype] NOT IN (35, 99, 34)
OPEN curAuditFields

FETCH NEXT FROM curAuditFields INTO @col, @AuditInsert, @AuditUpdate, @AuditDelete

SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #new FROM inserted
SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #old FROM deleted

WHILE @@FETCH_STATUS = 0 BEGIN

IF (@action = 'INSERTION' AND @AuditInsert = 1) OR
(@action = 'UPDATE' AND @AuditUpdate = 1) OR
(@action = 'DELETION' AND @AuditDelete = 1)
BEGIN

SET @sql = 'IF (SELECT [' + @col + '] FROM #new) <> (SELECT [' + @col + '] FROM #old) OR (SELECT COUNT(*) FROM #old) = 0 OR (SELECT COUNT(*) FROM #new) = 0 BEGIN' + CHAR(10) +
' DECLARE @old VARCHAR(50)' + CHAR(10) +
' DECLARE @new VARCHAR(50)' + CHAR(10) +
' DECLARE @RecordVal VARCHAR(50)' + CHAR(10) +

' SELECT @new = CAST([' + @col + '] AS VARCHAR(50)) FROM #new' + CHAR(10) +
' SELECT @old = CAST([' + @col + '] AS VARCHAR(50)) FROM #old' + CHAR(10) +
' SELECT @RecordVal = CAST([ID] AS VARCHAR(50)) FROM #old' + CHAR(10) +
' IF @RecordVal IS NULL BEGIN' + CHAR(10) +
' SELECT @RecordVal = CAST([ID] AS VARCHAR(50)) FROM #new' + CHAR(10) +
' END' + CHAR(10) +

' INSERT INTO AuditTable([Username], ModificationDate, SourceTable, ModifiedField, OldValue, NewValue, [Action], [RecordID])' + CHAR(10) +
' VALUES (''' + SUSER_SNAME(SUSER_SID()) + ''',''' + CAST(GETDATE() AS VARCHAR(20)) + ''', ''myTable'', ''' + @col + ''', @old, @new, ''' + @action + ''', @RecordVal)' + CHAR(10) +
'END'
EXEC (@sql)
END

FETCH NEXT FROM curAuditFields INTO @col, @AuditInsert, @AuditUpdate, @AuditDelete
END

DROP TABLE #new
DROP TABLE #old

CLOSE curAuditFields
DEALLOCATE curAuditFields
END
Oct 30 '07 #1
1 2053
ck9663
2,878 Expert 2GB
Hie.

I have a trigger that monitors changes to my table fields but I get an error saying subquery returned more than one value.Below is the code for my trigger, hope you will figure out whats happening..

CREATE TRIGGER trgmyTableAuditFields ON myTable
WITH ENCRYPTION
FOR INSERT, UPDATE, DELETE
AS
BEGIN

DECLARE @col VARCHAR(200)
DECLARE @primarykeycol VARCHAR(200)
DECLARE @sql VARCHAR(2000)
DECLARE @action VARCHAR(200)
DECLARE @AuditInsert BIT
DECLARE @AuditUpdate BIT
DECLARE @AuditDelete BIT

SELECT @action =
CASE
WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'INSERTION'
WHEN (SELECT COUNT(*) FROM inserted) = 0 THEN 'DELETION'
ELSE 'UPDATE'
END

DECLARE curAuditFields CURSOR FOR
SELECT c.[name], ac.[AuditInsert], ac.[AuditUpdate], ac.[AuditDelete]
FROM syscolumns c
INNER JOIN sysobjects o ON o.[id] = c.[id]
INNER JOIN aucAuditColumns ac ON ac.[Column] = c.[name] AND ac.[Table] = o.[name]
WHERE o.[name] = 'myTable'
AND c.[xtype] NOT IN (35, 99, 34)
OPEN curAuditFields

FETCH NEXT FROM curAuditFields INTO @col, @AuditInsert, @AuditUpdate, @AuditDelete

SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #new FROM inserted
SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #old FROM deleted

WHILE @@FETCH_STATUS = 0 BEGIN

IF (@action = 'INSERTION' AND @AuditInsert = 1) OR
(@action = 'UPDATE' AND @AuditUpdate = 1) OR
(@action = 'DELETION' AND @AuditDelete = 1)
BEGIN

SET @sql = 'IF (SELECT [' + @col + '] FROM #new) <> (SELECT [' + @col + '] FROM #old) OR (SELECT COUNT(*) FROM #old) = 0 OR (SELECT COUNT(*) FROM #new) = 0 BEGIN' + CHAR(10) +
' DECLARE @old VARCHAR(50)' + CHAR(10) +
' DECLARE @new VARCHAR(50)' + CHAR(10) +
' DECLARE @RecordVal VARCHAR(50)' + CHAR(10) +

' SELECT @new = CAST([' + @col + '] AS VARCHAR(50)) FROM #new' + CHAR(10) +
' SELECT @old = CAST([' + @col + '] AS VARCHAR(50)) FROM #old' + CHAR(10) +
' SELECT @RecordVal = CAST([ID] AS VARCHAR(50)) FROM #old' + CHAR(10) +
' IF @RecordVal IS NULL BEGIN' + CHAR(10) +
' SELECT @RecordVal = CAST([ID] AS VARCHAR(50)) FROM #new' + CHAR(10) +
' END' + CHAR(10) +

' INSERT INTO AuditTable([Username], ModificationDate, SourceTable, ModifiedField, OldValue, NewValue, [Action], [RecordID])' + CHAR(10) +
' VALUES (''' + SUSER_SNAME(SUSER_SID()) + ''',''' + CAST(GETDATE() AS VARCHAR(20)) + ''', ''myTable'', ''' + @col + ''', @old, @new, ''' + @action + ''', @RecordVal)' + CHAR(10) +
'END'
EXEC (@sql)
END

FETCH NEXT FROM curAuditFields INTO @col, @AuditInsert, @AuditUpdate, @AuditDelete
END

DROP TABLE #new
DROP TABLE #old

CLOSE curAuditFields
DEALLOCATE curAuditFields
END

i did not continue reading your code...am not sure how efficient it is to put a CUSOR inside a trigger. maybe you would want to find another way of doing this
Oct 30 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by sqlgoogle | last post: by
4 posts views Thread by solidsna2 | last post: by
10 posts views Thread by Shals | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.