Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.
Solved
can you help to update my trigger to work with multiple row inserted ?
Posted on 2012-12-22
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
7 Comments
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
http://stackoverflow.com/questions/2178889/sql-server-a-trigger-to-work-on-multiple-row-inserts
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
...
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.
i know what u do but can u help me how i can modify the
SET @message = (
SELECT rtrim(ltrim(message))
FROM inserted
)
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 ...?
Featured Post
We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month6 days, 22 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.