Triggers in SQL Server

I am newbie in SQL.I am reading Triggers created two trigger on a table.Below is my triggers-

CREATE trigger Test_TRIGGER_FOR
ON TEST_TABLE
for Insert
AS
if((Select ID from TEST_TABLE) =( Select I开发者_开发技巧D from INSERTED))
BEGIN
PRINT 'you have successful using FOR'
END
ELSE
BEGIN
PRINT 'ERROR AFTER'
END

CREATE trigger Test_TRIGGER_AFTER
ON TEST_TABLE
AFTER Insert
AS
if((Select ID from TEST_TABLE) =( Select ID from INSERTED))
BEGIN
PRINT 'you have successful using AFTER'
END
ELSE
BEGIN
PRINT 'ERROR AFTER'
END

I had created successfully Triggers but when I insert data in my table I am getting following error-

Msg 512, Level 16, State 1, Procedure Test_TRIGGER_FOR, 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.

Please tell me where I am wrong.

Thanks in advance.

On the line

if((Select ID from TEST_TABLE) =( Select ID from INSERTED)) 

the (Select ID from TEST_TABLE) will return more that one value if there are more than 1 entry in the table, which is not permitted in such a sub select.

It is slightly unclear what you are trying to do here though.

The problem with both triggers is this line:

if((Select ID from TEST_TABLE) =( Select ID from INSERTED))

It's unclear what you're trying to test for here, but both the TEST_TABLE and INSERTED tables can contain multiple rows.

The problem is becase you are doing a SELECT ID FROM TEST_TABLE which is returning multiple records. You need to add a WHERE clause to this in order to reduce it to 1 record for your test to succeed.

You could try something like:

DECLARE @id int;
SELECT @id = ID from INSERTED;

SELECT ID
  FROM TEST_TABLE
 WHERE ID = @id;

IF @@ROWCOUNT > 0
BEGIN 
    PRINT 'you have successful using FOR' 
END 
ELSE 
BEGIN 
    PRINT 'ERROR AFTER' 
END