Need trigger to split 1 filed to 4
-
Using mkeast's example (with some minor modifications) you can use the following to test which of these triggers work for multiple rows and which do not.
CREATE TABLE SMS
(
id int PRIMARY KEY IDENTITY(1,1)
, message varchar(100) NOT NULL
, part1 varchar(100) NULL
, part2 varchar(100) NULL
, part3 varchar(100) NULL
, part4 varchar(100) NULL
)
GO
CREATE TABLE MESSAGE
(
message varchar(100) not null
)
go
SET NOCOUNT ON
INSERT MESSAGE (message) VALUES ( '37,Text,200,90' )
INSERT MESSAGE (message) VALUES ( '123,This is a longer message. ,111,10' )
INSERT MESSAGE (message) VALUES ( '234,This message goes with 234,222,20' )
INSERT MESSAGE (message) VALUES ( '345,This message goes with 345,333,30' )
INSERT MESSAGE (message) VALUES ( '456,This message goes with 456,444,40' )
SET NOCOUNT OFF
GO
/*********** INSERT TRIGGER CODE HERE **************/
/********** END OF TRIGGER CODE *********************/
-- Test the trigger
SET NOCOUNT ON
INSERT SMS (message) SELECT message FROM MESSAGE
SET NOCOUNT OFF
SELECT * FROM SMS
GO
DROP TABLE SMS
GO
DROP TABLE MESSAGE
GO
-
Paul, you were absolutely correct!
I did not look at the code thoroughly and did not recognize that. 
Thank you for pointing that out. 
I have revisited my code and used Judith's and K Perkins idea's. The actual Trigger is pretty short now...
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SMS]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[SMS]
GO
CREATE TABLE [dbo].[SMS]( [id] int PRIMARY KEY IDENTITY(1,1),
message varchar(200) NOT NULL,
part1 varchar(100) NULL,
part2 varchar(100) NULL,
part3 varchar(100) NULL,
part4 varchar(100) NULL)
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Message]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[Message]
GO
CREATE TABLE [dbo].[Message] ( message varchar(200) NOT NULL)
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SMSSplitMessage]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[SMSSplitMessage]
GO
CREATE TRIGGER SMSSplitMessage
ON SMS
FOR INSERT
AS
BEGIN
DECLARE @Message varchar(81),
@CurrentID integer,
@MaxID integer
SELECT @MaxID = (SELECT MAX( [id]) FROM inserted)
SELECT @CurrentID = (SELECT MIN( [id]) FROM inserted)
WHILE @CurrentID <= @MaxID
BEGIN
SELECT @Message = (SELECT Message FROM inserted WHERE [id] = @CurrentID)
BEGIN TRANSACTION PartUpdate
UPDATE SMS SET
Part1 = REPLACE( PARSENAME( REPLACE( REPLACE( @Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 4), CHAR(124), CHAR(46)),
Part2 = REPLACE( PARSENAME( REPLACE( REPLACE( @Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 3), CHAR(124), CHAR(46)),
Part3 = REPLACE( PARSENAME( REPLACE( REPLACE( @Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 2), CHAR(124), CHAR(46)),
Part4 = REPLACE( PARSENAME( REPLACE( REPLACE( @Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 1), CHAR(124), CHAR(46))
FROM SMS
WHERE [id] = @CurrentID
COMMIT TRANSACTION PartUpdate
SELECT @CurrentID = (SELECT MIN( [id]) FROM inserted WHERE [id] > @CurrentID)
END
END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO Message VALUES( '37,Text,200,90')
INSERT INTO Message VALUES( '123,This is a longer message. ,111,10')
INSERT INTO Message VALUES( '234,This message goes with 234,222,20')
INSERT INTO Message VALUES( '345,This message goes with 345,333,30')
INSERT INTO Message VALUES( '456,This message goes with 456,444,40')
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO SMS( message) SELECT message FROM Message
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM SMS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE SMS
DROP TABLE Message
I wasn't born stupid - I had to study.
-
Why not try the update with concept like this ?
UPDATE SMS SET
sms.Part1 = REPLACE( PARSENAME( REPLACE( REPLACE( i.Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 4), CHAR(124), CHAR(46)),
sms.Part2 = REPLACE( PARSENAME( REPLACE( REPLACE( i.Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 3), CHAR(124), CHAR(46)),
sms.Part3 = REPLACE( PARSENAME( REPLACE( REPLACE( i.Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 2), CHAR(124), CHAR(46)),
sms.Part4 = REPLACE( PARSENAME( REPLACE( REPLACE( i.Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 1), CHAR(124), CHAR(46))
FROM SMS
inner join inserted i on sms.[id] = i.[id]
only and all the inserted records should update and there is no need for a loop.
-
Without a WHERE statement, I think you would be UPDATing the entire table...
I wasn't born stupid - I had to study.
-
As long as the [id] is unique/primarykey the inner join should update only those records in the inserted table that have just been inserted into the master table.
-
My head is not in the game on this one!
I was rushing out of work and again, misread the posting. You are correct and that would be more efficient. [Obviously, you would want to add some datatype conversions to this...]. I added an extra record into SMS to verify it will only update those inserted.
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SMS]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[SMS]
GO
CREATE TABLE [dbo].[SMS]( [id] int PRIMARY KEY IDENTITY(1,1),
message varchar(60) NOT NULL,
part1 varchar(10) NULL,
part2 varchar(35) NULL,
part3 varchar(10) NULL,
part4 varchar(10) NULL)
GO
INSERT INTO SMS( message) VALUES( '1,Not to be updated,100,1')
--------------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Message]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[Message]
GO
CREATE TABLE [dbo].[Message] ( message varchar(60) NOT NULL)
GO
--------------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SMSSplitMessage]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[SMSSplitMessage]
GO
CREATE TRIGGER SMSSplitMessage
ON SMS
FOR INSERT
AS
BEGIN
BEGIN TRANSACTION PartUpdate
UPDATE SMS SET
Part1 = RTRIM( LTRIM( REPLACE( PARSENAME( REPLACE( REPLACE( SMS.Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 4), CHAR(124), CHAR(46)))),
Part2 = RTRIM( LTRIM( REPLACE( PARSENAME( REPLACE( REPLACE( SMS.Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 3), CHAR(124), CHAR(46)))),
Part3 = RTRIM( LTRIM( REPLACE( PARSENAME( REPLACE( REPLACE( SMS.Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 2), CHAR(124), CHAR(46)))),
Part4 = RTRIM( LTRIM( REPLACE( PARSENAME( REPLACE( REPLACE( SMS.Message, CHAR(46), CHAR(124)), CHAR(44), CHAR(46)), 1), CHAR(124), CHAR(46))))
FROM SMS
INNER JOIN inserted ON( SMS.[id] = inserted.[ID])
COMMIT TRANSACTION PartUpdate
END
GO
--------------------------------------------------------------------------------------------------------
INSERT INTO Message VALUES( '37,Text,200,90')
INSERT INTO Message VALUES( '123,This is a longer message. ,111,10')
INSERT INTO Message VALUES( '234,This message goes with 234,222,20')
INSERT INTO Message VALUES( '345,This message goes with 345,333,30')
INSERT INTO Message VALUES( '456,This message goes with 456,444,40')
--------------------------------------------------------------------------------------------------------
INSERT INTO SMS( message) SELECT message FROM Message
--------------------------------------------------------------------------------------------------------
SELECT * FROM SMS
--------------------------------------------------------------------------------------------------------
DROP TABLE SMS
DROP TABLE Message
I wasn't born stupid - I had to study.
-
Only trying to point out a couple of concepts I haven't seen used much.
The function parsename and update on insert trigger inner joined to the inserted table are simple ways to accomplish this task. I was not trying to be specific in the implemention and am not suggesting they are the only ( or best for that matter) way to do this. They do, however make for relatively short code to get this particular job done. Hopefully we haven't strayed to far from the intent of the original post.
-
Hopefully not, but either way it taught me something...
I wasn't born stupid - I had to study.
-
Thanx to everyone who replied to my request... After strugling for hours to try and understand half of it this is what I settled on and it works great for what i needed:
CREATE TRIGGER trig_Split
ON dbo.SMS
FOR INSERT
AS
declare @STR varchar(200)
select @STR = (Select SMSMessage from inserted)
UPDATE SMS Set
Part1 = substring(@Str + '$', 0 + 1, charindex('$', @STR + '$', 0 + 1) - 0 - 1 ),
Part2 = substring(@Str + '$', charindex('$', @STR + '$') + 1, charindex('$', @STR + '$',
charindex('$', @STR + '$') + 1) - charindex('$', @STR + '$') - 1 ),
Part3 = substring(@Str + '$', charindex('$', @STR + '$', charindex('$', @STR + '$') + 1) + 1,
charindex('$', @STR + '$', charindex('$', @STR + '$',
charindex('$', @STR + '$') + 1) + 1) -
charindex('$', @STR + '$', charindex('$', @STR + '$') + 1) - 1 ),
Part4 = substring(@Str + '$', charindex('$', @STR + '$', charindex('$', @STR + '$',
charindex('$', @STR + '$') + 1) + 1) + 1, charindex('$', @STR + '$',
charindex('$', @STR + '$', charindex('$', @STR + '$',
charindex('$', @STR + '$') + 1) + 1) + 1) -
charindex('$', @STR + '$', charindex('$', @STR + '$',
charindex('$', @STR + '$') + 1) + 1) - 1 ),
Part5 = substring(@Str + '$', charindex('$', @STR + '$', charindex('$', @STR + '$',
charindex('$', @STR + '$', charindex('$', @STR + '$') + 1) + 1) + 1) + 1,
charindex('$', @STR + '$', charindex('$', @STR + '$', charindex('$', @STR + '$',
charindex('$', @STR + '$', charindex('$', @STR + '$') + 1) + 1) + 1) + 1) -
charindex('$', @STR + '$', charindex('$', @STR + '$',charindex('$', @STR + '$',
charindex('$', @STR + '$') + 1) + 1) + 1) - 1 )
Where ID = @@IDENTITY
It's ugly I know, but with my zero trigger knowledge it was all I could manage to figure out. Now all I need to do is to figure out to check if any naughty words are used in the sms. Maybe put all banned words in a seperate table and then do another update after the first one.
Will Update SMS Set Active=0 where Part2 Like (select naughtyword from Bossdontlikeit) work, and is it possible to use a Like % in this manner?
-
Tertius
If you change you code as highlighted below, your code will work for multiple record inserts - it currently does not.
CREATE TRIGGER trig_Split
ON dbo.SMS
FOR INSERT
AS
UPDATE SMS Set
Part1 = substring(SMS.SMSMessage+ '$', 0 + 1, charindex('$', SMS.SMSMessage+ '$', 0 + 1) - 0 - 1 ),
Part2 = substring(SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$') + 1, charindex('$', SMS.SMSMessage+ '$',
charindex('$', SMS.SMSMessage+ '$') + 1) - charindex('$', SMS.SMSMessage+ '$') - 1 ),
Part3 = substring(SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$') + 1) + 1,
charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$',
charindex('$', SMS.SMSMessage+ '$') + 1) + 1) -
charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$') + 1) - 1 ),
Part4 = substring(SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage + '$',
charindex('$', SMS.SMSMessage+ '$') + 1) + 1) + 1, charindex('$', SMS.SMSMessage+ '$',
charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$',
charindex('$', SMS.SMSMessage+ '$') + 1) + 1) + 1) -
charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$',
charindex('$', SMS.SMSMessage+ '$') + 1) + 1) - 1 ),
Part5 = substring(SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$', charindex('$', @SMSMessage+ '$',
charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$') + 1) + 1) + 1) + 1,
charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage + '$',
charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$') + 1) + 1) + 1) + 1) -
charindex('$', SMS.SMSMessage+ '$', charindex('$', SMS.SMSMessage+ '$',charindex('$', SMS.SMSMessage+ '$',
charindex('$', SMS.SMSMessage+ '$') + 1) + 1) + 1) - 1 )
FROM SMS INNER JOIN inserted i on SMS.ID = i.ID
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply