Solved

can you help to update my trigger to work with multiple row inserted ?

Posted on 2012-12-22
7
404 Views
Last Modified: 2013-01-09
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
Comment
Question by:afifosh
  • 4
  • 3
7 Comments
 
LVL 1

Author Comment

by:afifosh
ID: 38715774
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38716318
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
 
LVL 1

Author Comment

by:afifosh
ID: 38716614
and what about this ?
	SET @message = (
			SELECT rtrim(ltrim(message))
			FROM inserted
			)

Open in new window


what is the modification ?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38717588
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
 
LVL 1

Author Comment

by:afifosh
ID: 38717956
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
 
LVL 1

Author Comment

by:afifosh
ID: 38717971
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 38720183
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now