Posted by Erland Sommarskog on 09/02/06 15:45

Al Willis (alwillisj01@sbcglobal.net) writes:
> I've written an insert trigger to fill in data on 5 columns based on the
> key field column after a record is added to a table. The trigger works
> fine.

No, it doesn't. You will learn a second why.

> But what I also want to do is to write a stored procedure that
> will update the 5 columns for the entire table based on the table key
> field column. I'm new to both triggers and stored procedures and I
> can't figure out how to make a stored procedure do what I want.
>...
> SELECT @CABLENO_REMAIN = CABLENO FROM INSERTED

Try to insert more than one row at the same time. You will find that
the trigger will only handle one of inserted rows. A trigger fires once
per statement.

> SELECT @SEG1 = NULL
> SELECT @SEG2 = NULL
> SELECT @SEG3 = NULL
> SELECT @SEG4 = NULL
> SELECT @SEG5 = NULL
>
> SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)
> IF @DASH_POS > 0
> BEGIN
> SELECT @SEG1 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)
> SELECT @CABLENO_REMAIN = SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)

The correct strategy to is to simple insert the values to split up
into a temp table which also has SEG1, SEG2 etc as column. As well as
the DASH_POS. The you perform a number of UPDATE statements. You
may need to know:

UPDATE #tbl
SET SEG2 = CASE WHEN DASH_POS > 0 THEN substring(...) ELSE SEG2 END


--
Erland Sommarskog, SQL Server MVP, esquel@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

[Back to original message]