As far as I can see the problem is that a single
DELETE
statement can delete multiple rows. In such case the trigger is fired only once and the
deleted
table contains one row for each deleted row. So when you fetch value from the
deleted
table you get many rows.
Here's a simplified test case
CREATE TABLE TriggerTest (
col1 int
);
CREATE TRIGGER trg_TriggerTest_Delete ON TriggerTest
AFTER DELETE AS
BEGIN
DECLARE @col1 int;
SET @col1 = (SELECT col1 FROM DELETED);
PRINT @col1;
END;
INSERT INTO TriggerTest (col1) values
(1), (2), (3), (4), (5)
DELETE FROM TriggerTest WHERE col1 = 2;
DELETE FROM TriggerTest WHERE col1 IN (3,4);
The last delete returns with
Msg 512, Level 16, State 1, Procedure trg_TriggerTest_Delete, Line 5 [Batch Start Line 20]
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
As a fix I would try to remove the variable in the first place. If you're selecting data based on the deleted rows, could you simply have something like
CREATE TRIGGER TInfoDeleteTrigger ON [dbo].[TArtikl]
AFTER DELETE
AS
BEGIN
INSERT INTO TInfo_Performance_Queue
(F_Cod, FirstName, LastName, Guid, username, operation, datetime, editionno)
SELECT
F_Cod, FirstName, LastName, Guid, System_user,
'Delete row from TInfo', GetDate(),
dbo.GetMaxEditionNo(deleted.Guid)
FROM DELETED
END
[ADDED]
Another example including guid column
CREATE TABLE TriggerTest (
col1 int,
Guidcol uniqueidentifier default newid()
);
CREATE TRIGGER trg_TriggerTest_Delete ON TriggerTest
AFTER DELETE AS
BEGIN
SELECT deleted.guidcol from deleted;
END;
INSERT INTO TriggerTest (col1) values
(1), (2), (3), (4), (5)
DELETE FROM TriggerTest WHERE col1 IN (3,4);
Running the delete produces output like
guidcol
-------
4F1D6049-0558-4D2F-A81A-E207D8AC1795
F6B88232-3326-4F7C-8D36-64ED9F3CC826