SQL Server: getting error when insert trigger workes for update

I write a insert trigger which work is: when a row inserted into the table then it update a particular field by a 20 char unique number which is generated using time and random number. It working fine when I insert a single row. But problem happens when I insert multiple rows using a single insert statement.

My trigger & insert query and error are below:

Trigger:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [unique_outmsg_id] 
ON [dbo].[tbl_subscription]
FOR  INSERT
AS
BEGIN
  DECLARE @random_number varchar(6);
  DECLARE @time_value varchar(23);
  DECLARE @unique_id varchar(20);
  SELECT  @random_number = convert(varchar,(SELECT CAST(RAND() * 999 AS INT)));
  SELECT  @time_value =  convert(varchar,(select replace(replace(replace(replace(convert(varchar(23), getdate(), 121),'-',''),'.',''),' ',''),':','')));
  SELECT  @unique_id=(SELECT(@time_value+@random_number));   
  UPDATE dbo.tbl_subscription SET outmsg_id=@unique_id WHERE outbox_id=(SELECT outbox_id FROM inserted)
END

Output of the trigger: 20110724093323697833

Multiple row insert query:

 USE [test_abc] 
 INSERT INTO [test_abc].[dbo].[tbl_subscription] (inbox_id,inmsg_id,enabled) 
 SELECT s.inbox_id,s.enabled
 FROM   [test_def].[dbo].[tbl_subscriberlist] s,[test_def].[dbo].infoservice i 
 WHERE  s.mo_key = 'ABC' AND i.subscribtionKey='ABC'

Functionality of this query:

To collect the information from another table and insert into tbl_subscription multiple rows.

But the error is when I run this query:

Msg 512, Level 16, State 1, 开发者_JAVA技巧Procedure unique_outmsg_id, Line 13 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.

So, why this error is happen when I am inserting multiple rows into the table?? Any ideas? Please help.

---

Triggers must be written to handle multiple rows.

The problem is in this update statement:

UPDATE dbo.tbl_subscription 
SET outmsg_id=@unique_id 
WHERE outbox_id = (SELECT outbox_id FROM inserted)

It expects the subselect (SELECT outbox_id FROM inserted) to return only a single row (which of course it won't when the trigger is fired for a multi-row batch).

You need to rewrite your UPDATE statement as a join to the inserted table

• Multirow Considerations for DML Triggers

• Implementing DML Triggers

---

This is returning all rows. You can only select 1 values into a field

WHERE outbox_id = (SELECT outbox_id FROM inserted)

Instead try (I can't test it though)

UPDATE s
SET outmsg_id=@unique_id 
dbo.tbl_subscription s
JOIN inserted i
ON s.outbox_id=i.outbox_id

Furthermore this will always return the same number, which i imagine is not indended

SELECT  @random_number = convert(varchar,(SELECT CAST(RAND() * 999 AS INT)));  

Instead try:

SELECT  @random_number = convert(varchar,(CAST(RAND(ABS(CHECKSUM(NEWID())))*999 AS INT) AS INT)));  

(I recognize some of Mikael Eirikson's code in your statement)