Prev: OLEDB configuration when deploying multiple SSIS packages
Next: Help needed for Data formatting.
From: the_hawk_1 on 30 Jun 2008 13:51
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=(SELECT 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?
From: Erland Sommarskog on 30 Jun 2008 18:22
the_hawk_1 (hlajeunessse(a)gmail.com) writes:
> 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.

CREATE TRIGGER valide ON [dbo].[Test_hl_tmp] FOR INSERT AS

WITH newtels AS (
SELECT i.NOM, i.TEL, rowno = row_number() OVER(ORDER BY i.TEL)
FROM inserted i
WHERE NOT EXISTS (SELECT *
FROM test_hl_trig u
WHERE i.TEL = u.TEL)
)
UPDATE test_hl_trig
SET NOM = n.NOM,
TEL = n.TEL
FROM test_hl_trig t
JOIN newtels ON t.rownumber = n.rowno +
(SELECT COUNT(*)
FROM test_hl_trig t1
WHERE t1.TEL IS NOT NULL)

Notes:

o This solution requires SQL 2005. (Make it a habit to always say which
version of SQL Server you are using.)
o This is untested solution. For a tested solution, please post CREATE
TABLE statements for you tables, INSERT statements with sample data
and the desired result given the sample.
o I must say that the whole arrangement looks fragile to me, but without
knowledge of the business rules I cannot propose anything better.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: OLEDB configuration when deploying multiple SSIS packages
Next: Help needed for Data formatting.