asked on can you help to update my trigger to work with multiple row inserted ?
i have a table and this trigger please can you help me to update this trigger to work with multiple inserted row ?
USE [Peacock]
GO
/****** Object: Trigger [dbo].[ShortCodeMsgProccesing] Script Date: 12/22/2012 7:43:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[ShortCodeMsgProccesing] ON [dbo].[CGI_SMS_dShortCodeNumber]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE @usershortcode VARCHAR(16)
,@msisdn VARCHAR(16)
,@usermsgid BIGINT
,@message VARCHAR(200)
,@resultmsg VARCHAR(500)
,@keyword VARCHAR(10)
,@code VARCHAR(20)
-- Get the message content and the user mobile number and the ID Primary Key of the selected message
SET @message = (
SELECT Message
FROM inserted
)
SET @msisdn = (
SELECT MobileSubscriberNumber
FROM inserted
)
SET @usermsgid = (
SELECT ID
FROM inserted
)
SET @usershortcode = (
SELECT ShortCodeNumber
FROM inserted
)
DECLARE @ckmsg VARCHAR(300)
SET @ckmsg = (
SELECT Message
FROM inserted
WHERE dbo.RegexMatch('^¿(\s)+(\d{1,3})(\s)+([¿-¿a-zA-z]+)(\s)+([¿-¿a-zA-z]+)$', rtrim(ltrim(Message))) = '1'
)
DECLARE @refid VARCHAR(100)
SET @refid = 'U' + CONVERT(VARCHAR, @usermsgid)
-- We get the message
SET @message = (
SELECT rtrim(ltrim(message))
FROM inserted
)
IF NOT EXISTS (
SELECT *
FROM SubscriberProfile
WHERE MobileSubscriberNumber = @msisdn
AND ShortCodeNumber = @usershortcode
)
BEGIN
INSERT INTO SubscriberProfile (
[MobileSubscriberNumber]
,[ShortCodeNumber]
)
VALUES (
@msisdn
,@usershortcode
)
END
-- To get the potential code
-- SET @keyword = rtrim(LEFT(@message, 5))
-- If the code doesn't exist in our list of categories
-- SELECT @code = SubscriptionCode FROM Channels WHERE (SubscriptionCode LIKE '%' + @keyword+ '%')
-- Declare ChannelNumber
DECLARE @ChannelNumber VARCHAR(16)
,@ChannelName VARCHAR(50)
-- set the channel number by substring the message and get the right side from the message after the letter 'U' followed by a space.
--SET @ChannelNumber = RIGHT(@message, PATINDEX('%[^0-9]%', REVERSE(@message))-1)
-- 'unsubscription method' Check if the first letter of the message start with letter 'U' and check the other part of the message isnumeric.
IF UPPER(LEFT(@message, 1)) = 'U'
AND ISNUMERIC(REVERSE(LEFT(REVERSE(@message), CHARINDEX(CHAR(32), @message)))) = 1
BEGIN
SET @ChannelNumber = RIGHT(@message, PATINDEX('%[^0-9]%', REVERSE(@message)) - 1)
DECLARE @UserSubscriptionID INT
-- check if the user already subscribe to this channelnumber
-- IF EXISTS (SELECT * FROM Subscriptions WHERE ChannelID = @ChannelNumber AND MobileSubscriberNumber = @msisdn)
--IF EXISTS (SELECT * FROM Peacock.dbo.Subscriptions t,Peacock.dbo.Channels,Peacock.dbo.Services WHERE t.EndDate = ( SELECT MAX(EndDate) FROM Peacock.dbo.Subscriptions i WHERE i.MobileSubscriberNumber= t.MobileSubscriberNumber) AND Channels.ChannelID = CAST(t.ChannelID AS INT) AND Channels.ServiceID = Services.ServiceID AND t.SubscriptionStatus = 'Active' AND t.EndDate > GETDATE() AND Channels.ChannelID = @ChannelNumber AND t.MobileSubscriberNumber = @msisdn AND UnSubscriptionDate IS NULL)
IF EXISTS (
SELECT *
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.SubscriptionStatus = 'Active'
AND t.EndDate > GETDATE()
AND Channels.SubscriptionCode = @ChannelNumber
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND UnSubscriptionDate IS NULL
)
BEGIN
SET @UserSubscriptionID = (
SELECT t.SubscriptionID
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.SubscriptionStatus = 'Active'
AND t.EndDate > GETDATE()
AND Channels.SubscriptionCode = @ChannelNumber
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND UnSubscriptionDate IS NULL
)
DECLARE @unsubchannelnb BIGINT
SET @unsubchannelnb = (
SELECT i.ChannelID
FROM Channels i
,Services j
WHERE i.SubscriptionCode = @ChannelNumber
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
-- after confirmation that the mobilenumber is already subscribe to this service
--UPDATE Subscriptions SET SubscriptionStatus = 'unsubscription' WHERE MobileSubscriberNumber = @msisdn AND ChannelID = @ChannelNumber AND SubscriptionID = @UserSubscriptionID
--UPDATE Subscriptions SET UnSubscriptionDate = GETDATE() WHERE MobileSubscriberNumber = @msisdn AND ChannelID = @ChannelNumber AND SubscriptionID = @UserSubscriptionID
UPDATE Subscriptions
SET SubscriptionStatus = 'Unsubscribe'
,UnSubscriptionDate = GETDATE()
WHERE MobileSubscriberNumber = @msisdn
AND ChannelID = @unsubchannelnb
AND SubscriptionID = @UserSubscriptionID
SET @ChannelName = (
SELECT ChannelName
FROM Channels i
,Services j
WHERE i.SubscriptionCode = @ChannelNumber
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
SET @resultmsg = 'Your subscription to ' + @ChannelName + ' has been cancelled.'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,@UserSubscriptionID
,'U'
,'N'
)
END
ELSE
BEGIN
IF NOT EXISTS (
SELECT *
FROM Channels i
,Services j
WHERE i.SubscriptionCode = @ChannelNumber
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
BEGIN
-- if the user are not subscribed to this service
SET @resultmsg = 'The requested channel is not available.'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'U'
,'N'
)
END
ELSE
BEGIN
-- IF NOT EXISTS (SELECT * FROM Subscriptions WHERE ChannelID = @ChannelNumber AND MobileSubscriberNumber = @msisdn)
-- check if i need to remove unsubscription is null
IF NOT EXISTS (
SELECT *
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.SubscriptionStatus = 'Active'
AND t.EndDate > GETDATE()
AND Channels.SubscriptionCode = @ChannelNumber
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND UnSubscriptionDate IS NULL
)
BEGIN
-- if the user are not subscribed to this service
DECLARE @nchname VARCHAR(50)
SET @nchname = (
SELECT ChannelName
FROM Channels i
,Services j
WHERE i.SubscriptionCode = @ChannelNumber
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
SET @resultmsg = 'You are not subscribed to this channel.' + @nchname
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'U'
,'N'
)
END
ELSE
BEGIN
IF EXISTS (
SELECT *
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.SubscriptionStatus = 'Active'
AND t.EndDate > GETDATE()
AND Channels.SubscriptionCode = @ChannelNumber
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND UnSubscriptionDate IS NOT NULL
)
BEGIN
-- if the user are not subscribed to this service
DECLARE @nnchname VARCHAR(50)
SET @nnchname = (
SELECT ChannelName
FROM Channels i
,Services j
WHERE i.SubscriptionCode = @ChannelNumber
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
SET @resultmsg = 'You are already unsubscribed to ' + @nnchname
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'U'
,'N'
)
END
END
END
END
END
ELSE
BEGIN
IF (@message = '')
OR (@message IS NULL)
-- assign category for the user,
-- we chosen the "business news arabic"
BEGIN
IF EXISTS (
SELECT *
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.SubscriptionStatus = 'Active'
AND t.EndDate > GETDATE()
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND UnSubscriptionDate IS NULL
)
BEGIN
DECLARE @Makes VARCHAR(500)
SET @Makes = NULL
SELECT @Makes = COALESCE(@Makes + CHAR(13) + CHAR(10), + CHAR(13) + CHAR(10)) + CAST(Channels.SubscriptionCode AS VARCHAR) + '-' + Channels.ChannelName + CHAR(10)
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.SubscriptionStatus = 'Active'
AND t.EndDate > GETDATE()
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND UnSubscriptionDate IS NULL
SET @resultmsg = 'you are subscribed to this services:' + @Makes + + CHAR(13) + CHAR(10) + 'to unsubscribe send U then the channel code'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'empty'
,'N'
)
END
ELSE
BEGIN
DECLARE @Makess VARCHAR(500)
SET @Makess = NULL
SELECT @Makess = COALESCE(@Makess + CHAR(13) + CHAR(10), + CHAR(13) + CHAR(10)) + CAST(Channels.SubscriptionCode AS VARCHAR) + '-' + Channels.ChannelName + CHAR(10)
FROM Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE Channels.ServiceID = Services.ServiceID
AND Services.NonBillableShortCode = @usershortcode
SET @resultmsg = 'services list :' + @Makess + + CHAR(13) + CHAR(10) + 'to subscribe send the channel code'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'empty'
,'N'
)
/*
SET @resultmsg = 'we have sent an empty message'
INSERT INTO SMSProcessingResult(ResultMsg,MobileSubscriberNumber,ReferenceID,SubscriptionType,BillableStatus) VALUES (@resultmsg,@msisdn,@refid,'empty','N')
*/
END
END
ELSE
IF (@message = 'help')
-- assign category for the user,
-- we chosen the "business news arabic"
BEGIN
SET @resultmsg = 'you have send a help request'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'Help'
,'N'
)
END
ELSE
IF ISNUMERIC(@message) = 1
BEGIN
DECLARE @chid VARCHAR(16)
,@chname VARCHAR(16)
,@chenddate VARCHAR(50)
/*
SET @resultmsg = 'you have send a numeric value'
INSERT INTO SMSProcessingResult(ResultMsg,Msisdn) VALUES (@resultmsg,@msisdn)
*/
IF NOT EXISTS (
SELECT *
FROM Channels i
,Services j
WHERE i.SubscriptionCode = @message
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
BEGIN
-- if the user are not subscribed to this service
SET @resultmsg = 'The requested channel is not available.'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'S'
,'N'
)
END
ELSE
--IF NOT EXISTS (SELECT * FROM Subscriptions WHERE ChannelID = @message AND MobileSubscriberNumber = @msisdn)
IF NOT EXISTS (
SELECT *
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.EndDate > GETDATE()
AND Channels.SubscriptionCode = @message
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND UnSubscriptionDate IS NULL
)
BEGIN
SELECT @chname = i.ChannelName
,@chid = i.ChannelID
FROM Channels i
,Services j
WHERE (
i.SubscriptionCode = @message
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
--SET @chenddate = dateadd(day, +30, getdate())
SET @resultmsg = 'you have been subscribed in ' + @chname
DECLARE @InsertedSubscriptionID BIGINT
INSERT INTO Subscriptions (
ChannelID
,MobileSubscriberNumber
,ReferenceID
)
VALUES (
@chid
,@msisdn
,@refid
)
SET @InsertedSubscriptionID = (
SELECT SCOPE_IDENTITY()
)
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
,SubscriptionID
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'S'
,'Y'
,@InsertedSubscriptionID
)
END
ELSE
-- IF EXISTS (SELECT * FROM Subscriptions WHERE ChannelID = @message AND MobileSubscriberNumber = @msisdn)
IF EXISTS (
SELECT *
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.EndDate > GETDATE()
AND Channels.SubscriptionCode = @message
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND t.SubscriptionStatus = 'Active'
)
BEGIN
DECLARE @nnchnames VARCHAR(50)
SELECT @nnchnames = i.ChannelName
FROM Channels i
,Services j
WHERE (
i.SubscriptionCode = @message
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
-- set @nnchnames =(SELECT ChannelName FROM Channels WHERE ChannelID = @message)
SET @resultmsg = 'you have already subscribed in ' + @nnchnames
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'S'
,'N'
)
END
ELSE
IF EXISTS (
SELECT *
FROM Peacock.dbo.Subscriptions t
,Peacock.dbo.Channels
,Peacock.dbo.Services
WHERE t.EndDate = (
SELECT MAX(EndDate)
FROM Peacock.dbo.Subscriptions i
WHERE i.MobileSubscriberNumber = t.MobileSubscriberNumber
AND i.ChannelID = t.ChannelID
)
AND Channels.ChannelID = t.ChannelID
AND Channels.ServiceID = Services.ServiceID
AND t.EndDate > GETDATE()
AND Channels.SubscriptionCode = @message
AND t.MobileSubscriberNumber = @msisdn
AND Services.NonBillableShortCode = @usershortcode
AND t.SubscriptionStatus = 'Pending'
)
BEGIN
DECLARE @nnchnamess VARCHAR(50)
SELECT @nnchnamess = i.ChannelName
FROM Channels i
,Services j
WHERE (
i.SubscriptionCode = @message
AND i.ServiceID = j.ServiceID
AND j.NonBillableShortCode = @usershortcode
)
-- set @nnchnames =(SELECT ChannelName FROM Channels WHERE ChannelID = @message)
SET @resultmsg = 'your subscription status for ' + @nnchnamess + ' is pending '
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'S'
,'N'
)
END
ELSE
BEGIN
SET @resultmsg = 'subscription error please send the word help to get details about subscription'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'S'
,'N'
)
END
END
ELSE
IF EXISTS (
SELECT Message
FROM inserted
WHERE dbo.RegexMatch('^¿(\s)+(\d{1,3})(\s)+([¿-¿a-zA-z]+)(\s)+([¿-¿a-zA-z]+)$', rtrim(ltrim(Message))) = '1'
)
BEGIN
UPDATE CGI_SMS_dShortCodeNumber
SET SetID = '1'
WHERE ID = @usermsgid
DECLARE @val NVARCHAR(3000)
SET @val = (
SELECT rtrim(ltrim(message))
FROM inserted
)
DECLARE @age NVARCHAR(10)
DECLARE @sex NVARCHAR(10)
DECLARE @loc NVARCHAR(100)
DECLARE @cage VARCHAR(10)
DECLARE @pos INT
DECLARE @npos INT
WHILE charindex(' ', @val) > 0
BEGIN
SET @val = replace(@val, ' ', ' ')
END
IF LEFT(@val, 1) = '¿'
AND @val LIKE N'¿ % % %'
BEGIN
SET @pos = 3
SET @npos = charindex(' ', @val, @pos)
SET @age = substring(@val, @pos, @npos - @pos)
SET @pos = @npos + 1
SET @npos = charindex(' ', @val, @pos)
SET @sex = substring(@val, @pos, @npos - @pos)
SET @pos = @npos + 1
SET @npos = len(@val)
SET @loc = substring(@val, @pos, @npos)
SET @pos = @npos + 1
IF ISNUMERIC(@age) = 1
BEGIN
SET @cage = CONVERT(BIGINT, YEAR(GETDATE())) - @age
UPDATE SubscriberProfile
SET Gender = @sex
,BirthYear = @cage
,RegionName = @loc
WHERE MobileSubscriberNumber = @msisdn
AND ShortCodeNumber = @usershortcode
END
--select @age as age,@sex as sex,@loc as location
END
DECLARE @Makesss VARCHAR(500)
SET @Makesss = NULL
SELECT @Makesss = COALESCE(@Makesss + CHAR(13) + CHAR(10), + CHAR(13) + CHAR(10)) + ': ¿¿¿ ¿¿ ¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿ ' + CHAR(13) + CHAR(10) + ' ¿¿¿¿¿ : ' + CAST(CONVERT(BIGINT, YEAR(GETDATE())) - SubscriberProfile.BirthYear AS VARCHAR) + CHAR(13) + CHAR(10) + ' ¿¿¿¿¿ : ' + CAST(SubscriberProfile.Gender AS VARCHAR) + CHAR(10) + CHAR(13) + CHAR(10) + ' ¿¿¿¿¿¿¿ : ' + SubscriberProfile.RegionName + CHAR(10)
FROM SubscriberProfile
WHERE MobileSubscriberNumber = @msisdn
AND ShortCodeNumber = @usershortcode
SET @resultmsg = 'you profile information has been submited'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@Makesss
,@msisdn
,@refid
,'error'
,'N'
)
END
ELSE
BEGIN
SET @resultmsg = 'please send a blank message to get help'
INSERT INTO SMSProcessingResult (
ResultMsg
,MobileSubscriberNumber
,ReferenceID
,SubscriptionType
,BillableStatus
)
VALUES (
@resultmsg
,@msisdn
,@refid
,'error'
,'N'
)
END
END
END
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
Last Comment
Anthony Perkins
It needs to look like this:
ALTER TRIGGER [dbo].[ShortCodeMsgProccesing] ON [dbo].[CGI_SMS_dShortCodeNumber]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1
FROM SubscriberProfile s
INNER JOIN INSERTED i ON s.MobileSubscriberNumber = i.MobileSubscriberNumber
AND s.ShortCodeNumber = i.ShortCodeNumber )
BEGIN
INSERT INTO SubscriberProfile
([MobileSubscriberNumber],
[ShortCodeNumber]
)
SELECT MobileSubscriberNumber,
ShortCodeNumber
FROM Inserted
END
...
ASKER
and what about this ?
what is the modification ?
SET @message = (
SELECT rtrim(ltrim(message))
FROM inserted
)
what is the modification ?
Don't use that structure as you want to be able to support multiple rows. You need to do an INNER JOIN against the INSERTED table like I showed you.
ASKER
i know what u do but can u help me how i can modify the
SET @message = (
SELECT rtrim(ltrim(message))
FROM inserted
)
ASKER
IF EXISTS (SELECT *
FROM CGI_SMS_dShortCodeNumber s
INNER JOIN INSERTED i ON s.MobileSubscriberNumber = i.MobileSubscriberNumber AND I.ID = s.ID )
BEGIN
./* HERE I WILL PROCESS EACH RECORDS INSERTED ?? */
END
I AM BEGINNER IN SQL . each row inserted on the table CGI_SMS_dShortCodeNumber should be processed using this trigger by check the content of each message field..
for each inserted row i should select the required field to do some task . in each row
like this
the modification should be ???
but in the last modification the @ckmsg is like a table contain many message ...?
FROM CGI_SMS_dShortCodeNumber s
INNER JOIN INSERTED i ON s.MobileSubscriberNumber = i.MobileSubscriberNumber AND I.ID = s.ID )
BEGIN
./* HERE I WILL PROCESS EACH RECORDS INSERTED ?? */
END
I AM BEGINNER IN SQL . each row inserted on the table CGI_SMS_dShortCodeNumber should be processed using this trigger by check the content of each message field..
for each inserted row i should select the required field to do some task . in each row
like this
SET @ckmsg = (
SELECT Message
FROM inserted
WHERE dbo.RegexMatch('^¿(\s)+(\d{1,3})(\s)+([¿-¿a-zA-z]+)(\s)+([¿-¿a-zA-z]+)$', rtrim(ltrim(Message))) = '1'
).
the modification should be ???
SET @ckmsg = (
SELECT Message
FROM CGI_SMS_dShortCodeNumber s
INNER JOIN INSERTED i ON s.MobileSubscriberNumber = i.MobileSubscriberNumber AND I.ID = s.ID
WHERE dbo.RegexMatch('^¿(\s)+(\d{1,3})(\s)+([¿-¿a-zA-z]+)(\s)+([¿-¿a-zA-z]+)$', rtrim(ltrim(Message))) = '1'
).
but in the last modification the @ckmsg is like a table contain many message ...?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
http://stackoverflow.com/questions/2178889/sql-server-a-trigger-to-work-on-multiple-row-inserts