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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER TRIGGER [dbo].[ShortCodeMsgProccesing] ON [dbo].[CGI_SMS_dShortCodeNumber]FOR INSERTASBEGIN -- 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 ENDEND
ALTER TRIGGER [dbo].[ShortCodeMsgProccesing] ON [dbo].[CGI_SMS_dShortCodeNumber] FOR INSERTAS 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...
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
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' ).
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' ).
http://stackoverflow.com/questions/2178889/sql-server-a-trigger-to-work-on-multiple-row-inserts