By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,135 Members | 1,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,135 IT Pros & Developers. It's quick & easy.

Stored Procedure To Update Columns

P: n/a
Hello,

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.
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.

Can someone help me to get me started? Here is the trigger code that I am
trying to get to work as a stored procedure. All I'm trying to do is to
break up an entry that contains dashes into separate fields that are
delimited by the dashes. As an example if field CABLENO is equal to
I-IJB-200-45, then I want to break that up into 4 separate fields containing
I, IJB, 200, 45 respectively.

Thanks,

Al Willis
--------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='CABLE_SEGMENTS' AND TYPE=
'TR')
DROP TRIGGER IM.CABLE_SEGMENTS
GO
CREATE TRIGGER CABLE_SEGMENTS
ON IM.CAB_M
FOR INSERT
AS
DECLARE @CABLENO_REMAIN VARCHAR(40),
@DASH_POS SMALLINT,
@SEG1 VARCHAR(40),
@SEG2 VARCHAR(40),
@SEG3 VARCHAR(40),
@SEG4 VARCHAR(40),
@SEG5 VARCHAR(40)

SELECT @CABLENO_REMAIN = CABLENO FROM INSERTED
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)

SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)
IF @DASH_POS 0
BEGIN
SELECT @SEG2 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)
SELECT @CABLENO_REMAIN = SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)

SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)
IF @DASH_POS 0
BEGIN
SELECT @SEG3 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)
SELECT @CABLENO_REMAIN =
SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)

SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)
IF @DASH_POS 0
BEGIN
SELECT @SEG4 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)
SELECT @CABLENO_REMAIN =
SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)

SELECT @SEG5 = @CABLENO_REMAIN
END
ELSE
SELECT @SEG4 = @CABLENO_REMAIN
END
ELSE
SELECT @SEG3 = @CABLENO_REMAIN
END
ELSE
SELECT @SEG2 = @CABLENO_REMAIN
END
ELSE
SELECT @SEG1 = @CABLENO_REMAIN

UPDATE IM.CAB_M SET CABLESEG1 = @SEG1,CABLESEG2 = @SEG2,CABLESEG3 =
@SEG3,CABLESEG4 = @SEG4,CABLESEG5 = @SEG5
FROM INSERTED INS
WHERE IM.CAB_M.CABLENO = INS.CABLENO

GO

--------------------------------------------------------------------------------------------------------
Sep 2 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Al Willis (al*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 2 '06 #2

P: n/a
Erland,

Thanks for your helpful and quick response.

Regards,

Al
Sep 2 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.