How can I do that using a trigger

[the_hawk_1]

Hi!

I've got a table where an index number is already given.


I want to establish a trigger on a tmp table, so each time rows are
inserted, the trigger verify the next number available in the main
table, and then update it.


I've tried this:
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER valide ON [dbo].[Test_hl_tmp]
FOR INSERT
AS
DECLARE @TEL as char(10)
DECLARE @NOM as char (10)
SET @NOM = (SELECT NOM FROM inserted i)
SET @TEL = (SELECT TEL FROM inserted i)


BEGIN
IF (@TEL not in (SELECT @TEL FROM test_hl_trig))
BEGIN
UPDATE test_hl_trig
SET NOM = @NOM,
TEL = @TEL,
DATE_IMPORT = getdate()
WHERE recordnum=(SELE CT count(*)+1 FROM test_hl_trig WHERE TEL is not
null)
END
END
--------------------------------------------------------------------------------------------------------------------------------------
It was working well when inserting a single row, but as soon as I
were
inserting more than that, I've got this "Server: Msg 512, Level 16,
State 1, Procedure valide, Line 6
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."


How can I do that?