Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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

Open in new window

0
afifosh
Asked:
afifosh
  • 4
  • 3
1 Solution
 
afifoshAuthor Commented:
i don't want to use like this solution if we have another better solution or idea ..

http://stackoverflow.com/questions/2178889/sql-server-a-trigger-to-work-on-multiple-row-inserts
0
 
Anthony PerkinsCommented:
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

... 

Open in new window

I will leave the rest as an exercise.
0
 
afifoshAuthor Commented:
and what about this ?
	SET @message = (
			SELECT rtrim(ltrim(message))
			FROM inserted
			)

Open in new window


what is the modification ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
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.
0
 
afifoshAuthor Commented:
i know what u do but can u help me how i can modify the
SET @message = (
			SELECT rtrim(ltrim(message))
			FROM inserted
			)

Open in new window

to added the inner join please
0
 
afifoshAuthor Commented:
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'
			).

Open in new window


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

Open in new window

how i can know which row inserted are in proccessing
but in the last modification the @ckmsg is like a table contain many message ...?
0
 
Anthony PerkinsCommented:
I am sorry but that is way too much work.  I would need the schema for all your tables and around 2 to 4 hours to figure out the exact requirements and write the code.  If you cannot get any help here, I would suggest you hire a local experienced SQL DBA to do it and show you how.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now