Tuesday, November 4, 2008

Solved Problem: SQL Error on Triggers with sub-triggers or sub-queries...

Trigger SQL Statement:
CREATE TRIGGER [dbo].[tr_batch_delete] ON [dbo].[tr_batch]
FOR DELETE
AS
BEGIN
DELETE FROM tr_month WHERE tr_year = (SELECT tr_year FROM Deleted) AND tr_month = (SELECT tr_month FROM Deleted) AND code_country = (SELECT code_country FROM Deleted)
DELETE FROM tr_factor WHERE tr_year = (SELECT tr_year FROM Deleted) AND tr_month = (SELECT tr_month FROM Deleted) AND code_country = (SELECT code_country FROM Deleted)
DELETE FROM tr_adjust WHERE tr_year = (SELECT tr_year FROM Deleted) AND tr_month = (SELECT tr_month FROM Deleted) AND code_country = (SELECT code_country FROM Deleted)
END;

Error:
Server: Msg 512, Level 16, State 1, Procedure tr_batch_delete, Line 5
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.

Solution:
Replace = with IN

No comments: