|
|
Posted by Al Willis on 09/02/06 11:47
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
--------------------------------------------------------------------------------------------------------
Navigation:
[Reply to this message]
|