0

Dear all,
I have 2 tables :

create table test
(
	FieldA varchar(10),	FieldB varchar(10),	FieldC varchar(10)
)
and
create table test_trigger
(
	FieldA varchar(10),	FieldB varchar(10),	FieldC varchar(10)
)

I create Insert Trigger in test table. Every time test table is inserted new row, automatically inserted in test_trigger table.

insert into test values('3','33','333');
insert into test values('4','44','444');

I have created the Trigger, it only work for one row not multiple rows.
I don't know why multiple rows trigger don't work. I only copy from manual book.

So, Could you help me to check my trigger script ?

Thanks,

Kusno.

CREATE TRIGGER trg_Test_insert ON Test FOR INSERT AS
DECLARE @FieldA AS VARCHAR(10);

DECLARE @rc AS INT;
SET @rc = @@rowcount;

IF @rc = 0 RETURN;
IF @rc = 1
   BEGIN
     SELECT @FieldA= FieldA FROM inserted;
     INSERT INTO test_trigger select * from test where FieldA = @FieldA;  
   END
ELSE
BEGIN
  SELECT * INTO #I FROM inserted;
  --CREATE UNIQUE CLUSTERED INDEX idx_keycol ON #I(FieldA);
  SELECT @FieldA = FieldA FROM (SELECT TOP (1) FieldA FROM #I ORDER BY FieldA) AS D;
  WHILE @@rowcount > 0
  BEGIN
       INSERT INTO test_trigger select * from test where FieldA = @FieldA;  
      SELECT @FieldA = FieldA FROM (SELECT TOP (1) FieldA FROM #I ORDER BY FieldA) AS D;
  END
END
GO
2
Contributors
1
Reply
3
Views
9 Years
Discussion Span
Last Post by Westchaser
0

The virtual INSERTED table can contain more than one row.

Try using: .... where yourkey in (select keyvalue from INSERTED)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.