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
1 2220 ck9663 2,878
Recognized Expert Specialist
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Nachi |
last post by:
Hi,
Urgent Help appreciated....
I am getting resultset with first condition and when try to get the
resutlset from second condition i am getting the above error in
SQL200.
I know that i am...
|
by: sqlgoogle |
last post by:
Hi I'm trying to update a db based on the select statement which has
ORDER BY in it.
And due to that I'm getting error which states that
Server: Msg 1033, Level 15, State 1, Line 13
The ORDER...
|
by: Tim Marshall |
last post by:
A2003. I am getting this error message when I try to set a report's
recordsource to an SQL statement or a saved querydef that uses sub-queries.
I've debug.printed the SQL, and run it as a stand...
|
by: solidsna2 |
last post by:
Hi,
I am relatively new to SQL. I am using SQL 2000. I am trying to
Update a field base in a criteria in a scond table.
UPDATE Tbl1
SET Tbl1.Row2 = '1'
WHERE Tbl1.Row1...
|
by: Shals |
last post by:
Hi
I'm using Command
DoCmd.OpenView "dbo.vFDXQryBldgEdit3",acViewNormal,acEdit
to open a view vFDXQryBldgEdit3 (written in SQL Server) in the Edit
mode. This view is created on single table....
| |
by: yanksRoll |
last post by:
Hi,
I'm having a problem with the following trigger:
CREATE TRIGGER tr_FamID
ON dbo.myFirstTbl
FOR INSERT
AS
DECLARE @address2 VARCHAR (50)
DECLARE @compareFamID INT
|
by: jideesh |
last post by:
---------------trigger code
create trigger DeletepurchaseItems on table_purchaseitems
for delete,update
as
begin
select * from deleted
update table_STOCK set ostock=(ostock-(select sqty from...
|
by: codedhacker |
last post by:
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement:
DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' )...
|
by: rajesh0303 |
last post by:
Query gives error : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression
Query is :
select...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |