Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
Solved
trigger on insert to decompose record in different table refering to reqport
Posted on 2013-01-03
i have the following table cgi_sms_received
and in this table i will store many record on it and than i run a stored procedure to distribute record to other table referring to the reqport than when i finish i will remove all records from cgi_sms_received
the stored procedure code is
the structure of the cgi_sms_recived is
it's better to run a trigger on this table to distribute record in other table ?
i want a way for good performance
and in this table i will store many record on it and than i run a stored procedure to distribute record to other table referring to the reqport than when i finish i will remove all records from cgi_sms_received
the stored procedure code is
USE [CGI_SMS_SERVICES]
GO
/****** Object: StoredProcedure [dbo].[CGI_SMS_Services_MainTrigger] Script Date: 01/03/2013 18:00:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CGI_SMS_Services_MainTrigger]
AS
set nocount on
begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin transaction
declare @rec_id int
declare @reqport bigint
declare @recdport bigint
declare @message nvarchar(4000)
declare @msisdn varchar(50)
declare @coding int
declare @msg nvarchar(4000)
DECLARE @thedatetime datetime
DECLARE @thedate char(8)
DECLARE @thetime char(6)
declare @thedate1 char(8)
declare @thetime1 char(6)
SELECT @thedatetime = getdate()
SELECT @thedate = STR(Datepart(yyyy, @thedatetime), 4) + STR(Datepart(mm, @thedatetime), 2) + STR(Datepart(dd, @thedatetime), 2)
SELECT @thetime = STR(Datepart(hh, @thedatetime), 2) + STR(Datepart(mi, @thedatetime), 2) + STR(Datepart(ss, @thedatetime), 2)
SELECT @thedate = REPLACE(@thedate, ' ', '0')
SELECT @thetime = REPLACE(@thetime, ' ', '0')
declare Received_cursor cursor for
SELECT top 20 ID,ReqPort,RecdPort,Message,Msisdn,coding FROM cgi_sms_received --or msisdn !='966532532832' --order by reqport desc
open Received_cursor
fetch next from Received_cursor into
@rec_id,@reqport,@recdport,@message,@msisdn,@coding
WHILE @@FETCH_STATUS = 0
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin transaction
set @msg = ''
exec cgi_sms_services..spsms_junkcharacters @message, @msg output
set @message = @msg
if(ltrim(rtrim(@reqport)) = 732222) begin
insert into CGI_SMS_d732222(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752202) begin
insert into CGI_SMS_d752202(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752267) begin
insert into CGI_SMS_d752267(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752266) begin
insert into CGI_SMS_d752266(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752265) begin
insert into CGI_SMS_d752265(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752264) begin
insert into CGI_SMS_d752264(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 742268) begin
insert into CGI_SMS_d742268(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 742267) begin
insert into CGI_SMS_d742267(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 742266) begin
insert into CGI_SMS_d742266(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 742265) begin
insert into CGI_SMS_d742265(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 742264) begin
insert into CGI_SMS_d742264(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 732266) begin
insert into CGI_SMS_d732266(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 730164) begin
insert into CGI_SMS_d730164(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 732265) begin
insert into CGI_SMS_d732265(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 732264) begin
insert into CGI_SMS_d732264(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 702210) begin
insert into CGI_SMS_d702210(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 702200) begin
insert into CGI_SMS_d702200(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 702227) begin
insert into CGI_SMS_d702227(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 740164) begin
insert into CGI_SMS_d740164 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 750164) begin
insert into CGI_SMS_d750164 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 730165) begin
insert into CGI_SMS_d730165 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 740165) begin
insert into CGI_SMS_d740165 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 750165) begin
insert into CGI_SMS_d750165 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 730166) begin
insert into CGI_SMS_d730166 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 732267) begin
insert into CGI_SMS_d732267 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 740166) begin
insert into CGI_SMS_d740166 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 750166) begin
insert into CGI_SMS_d750166 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 730167) begin
insert into CGI_SMS_d730167 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 740167) begin
insert into CGI_SMS_d740167 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 750167) begin
insert into CGI_SMS_d750167 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 740168) begin
insert into CGI_SMS_d740168 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 732201) begin
insert into CGI_SMS_d732201 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 742202) begin
insert into CGI_SMS_d742202 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752203) begin
insert into CGI_SMS_d752203 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752204) begin
insert into CGI_SMS_d752204 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752205) begin
insert into CGI_SMS_d752205 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 712226) begin
insert into CGI_SMS_d712226 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752200) begin
insert into CGI_SMS_d752200 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752206) begin
insert into CGI_SMS_d752206 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752207) begin
insert into CGI_SMS_d752207 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 752208) begin
insert into CGI_SMS_d752208 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 702221) begin
insert into CGI_SMS_d702221 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 702222) begin
insert into CGI_SMS_d702222 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 702223) begin
insert into CGI_SMS_d702223 (reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 702224) begin
insert into CGI_SMS_d702224(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 702225) begin
insert into CGI_SMS_d702225(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
if(ltrim(rtrim(@reqport)) = 84400) begin
print 'befor insert into 84400'
insert into CGI_SMS_d84400(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
print 'after insert into 84400'
end
if(ltrim(rtrim(@reqport)) = 815677) begin
insert into CGI_SMS_d815677(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
else if(ltrim(rtrim(@reqport)) = 84401) begin
insert into CGI_SMS_d84401(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84402) begin
insert into CGI_SMS_d84402(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84403) begin
insert into CGI_SMS_d84403(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84404) begin
insert into CGI_SMS_d84404(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84405) begin
insert into CGI_SMS_d84405(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84406) begin
insert into CGI_SMS_d84406(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84407) begin
insert into CGI_SMS_d84407(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84408) begin
insert into CGI_SMS_d84408(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84409) begin
insert into CGI_SMS_d84409(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84410) begin
insert into CGI_SMS_d84410(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84411) begin
insert into CGI_SMS_d84411(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84412) begin
insert into CGI_SMS_d84412(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84413) begin
insert into CGI_SMS_d84413(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84414) begin
insert into CGI_SMS_d84414(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84415) begin
insert into CGI_SMS_d84415(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
else if(ltrim(rtrim(@reqport)) = 84416) begin
insert into CGI_SMS_d84416(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
else if(ltrim(rtrim(@reqport)) = 84417) begin
insert into CGI_SMS_d84417(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84418) begin
insert into CGI_SMS_d84418(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84419) begin
insert into CGI_SMS_d84419(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
else if(ltrim(rtrim(@reqport)) = 84420) begin
insert into CGI_SMS_d84420(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84421) begin
insert into CGI_SMS_d84421(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84422) begin
insert into CGI_SMS_d84422(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
else if(ltrim(rtrim(@reqport)) = 84423) begin
insert into CGI_SMS_d84423(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84424) begin
insert into CGI_SMS_d84424(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84425) begin
insert into CGI_SMS_d84425(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84426) begin
insert into CGI_SMS_d84426(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84427) begin
insert into CGI_SMS_d84427(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84431) begin
insert into CGI_SMS_d84431(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84432) begin
insert into CGI_SMS_d84432(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84433) begin
insert into CGI_SMS_d84433(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84434) begin
insert into CGI_SMS_d84434(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84435) begin
insert into CGI_SMS_d84435(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84436) begin
insert into CGI_SMS_d84436(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84437) begin
insert into CGI_SMS_d84437(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84438) begin
insert into CGI_SMS_d84438(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84439) begin
insert into CGI_SMS_d84439(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84440) begin
insert into CGI_SMS_d84440(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84441) begin
insert into CGI_SMS_d84441(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84443) begin
insert into CGI_SMS_d84443(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84444 ) begin
insert into CGI_SMS_d84444(reqport,recdport,message,msisdn,coding) values (@reqport,@recdport,@message,@msisdn,@coding)
end
else if(ltrim(rtrim(@reqport)) = 84445) begin
insert into CGI_SMS_d84445(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84446) begin
insert into CGI_SMS_d84446(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84447) begin
insert into CGI_SMS_d84447(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84448) begin
insert into CGI_SMS_d84448(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84450) begin
insert into CGI_SMS_d84450(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84451) begin
insert into CGI_SMS_d84451(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84454) begin
insert into CGI_SMS_d84454(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84455) begin
insert into CGI_SMS_d84455(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84456) begin
insert into CGI_SMS_d84456(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84457) begin
insert into CGI_SMS_d84457(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84460) begin
insert into CGI_SMS_d84460(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84461) begin
insert into CGI_SMS_d84461(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84464) begin
insert into CGI_SMS_d84464(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84465) begin
insert into CGI_SMS_d84465(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84466) begin
insert into CGI_SMS_d84466(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84474) begin
insert into CGI_SMS_d84474(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84475) begin
insert into CGI_SMS_d84475(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84477) begin
insert into CGI_SMS_d84477(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84481) begin
insert into CGI_SMS_d84481(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84484) begin
insert into CGI_SMS_d84484(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84485) begin
insert into CGI_SMS_d84485(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84486) begin
insert into CGI_SMS_d84486(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84488) begin
insert into CGI_SMS_d84488(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84494) begin
insert into CGI_SMS_d84494(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84500) begin
insert into CGI_SMS_d84500(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 86366) begin
insert into CGI_SMS_d86366(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 86367) begin
insert into CGI_SMS_d86367(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 86377) begin
insert into CGI_SMS_d86377(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 86378) begin
insert into CGI_SMS_d86378(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
END
else if(ltrim(rtrim(@reqport)) = 855666) begin
insert into CGI_SMS_d855666(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
END
else if(ltrim(rtrim(@reqport)) = 855661) begin
insert into CGI_SMS_d855661(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
END
else if(ltrim(rtrim(@reqport)) = 855662) begin
insert into CGI_SMS_d855662(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84480) begin
insert into CGI_SMS_d84480(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84499) begin
insert into CGI_SMS_d84499(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 84430) begin
insert into CGI_SMS_d84430(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6032) begin
insert into CGI_SMS_d6032(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6034) begin
insert into CGI_SMS_d6034(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 670128) begin
insert into CGI_SMS_d670128(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6035) begin
insert into CGI_SMS_d6035(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 640024) begin
insert into CGI_SMS_d640024(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6037) begin
insert into CGI_SMS_d6037(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 630529) begin
insert into CGI_SMS_d630529(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6346) begin
insert into CGI_SMS_d6346(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 670131) begin
insert into CGI_SMS_d670131(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6392) begin
insert into CGI_SMS_d6392(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6456) begin
insert into CGI_SMS_d6456(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 630589) begin
insert into CGI_SMS_d630589(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6457) begin
insert into CGI_SMS_d6457(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 630590) begin
insert into CGI_SMS_d630590(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6864) begin
insert into CGI_SMS_d6864(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 670077) begin
insert into CGI_SMS_d670077(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6865) begin
insert into CGI_SMS_d6865(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 600136) begin
insert into CGI_SMS_d600136(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 6871) begin
insert into CGI_SMS_d6871(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 87101) begin
insert into CGI_SMS_d87101(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 87102) begin
insert into CGI_SMS_d87102(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 87111) begin
insert into CGI_SMS_d87111(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 87171) begin
insert into CGI_SMS_d87171(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 635812) begin
insert into CGI_SMS_d635812(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 624342) begin
insert into CGI_SMS_d624342(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 656871) begin
insert into CGI_SMS_d656871(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 601094) begin
insert into CGI_SMS_d601094(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 670076) begin
insert into CGI_SMS_d670076(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
else if(ltrim(rtrim(@reqport)) = 10001) begin
insert into CGI_SMS_d10001(reqport,recdport,message,msisdn,coding, datein, timein) values (@reqport,@recdport,@message,@msisdn,@coding, @thedate, @thetime)
end
insert into CGI_SMS_RECEIVED_history(ReqPort,RecdPort,Message,MSISDN,Datein,Timein,coding) values (@reqport,@recdport,@message,@msisdn,@thedate,@thetime,@coding)
Delete CGI_SMS_RECEIVED where id=@rec_id
fetch next from Received_cursor into
@rec_id,@reqport,@recdport,@message,@msisdn,@coding
commit transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END
close Received_cursor
deallocate Received_cursor
/*
Commit Trasnaction
*/
commit transaction
/*
Set the isolation level to default
*/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
end
the structure of the cgi_sms_recived is
USE [CGI_SMS_SERVICES]
GO
/****** Object: Table [dbo].[CGI_SMS_RECEIVED] Script Date: 01/03/2013 18:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CGI_SMS_RECEIVED](
[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ReqPort] [bigint] NULL,
[RecdPort] [bigint] NULL,
[Message] [nvarchar](4000) NULL,
[MSISDN] [varchar](25) NULL,
[DateOfRequest] [char](8) NULL,
[TimeOfRequest] [char](6) NULL,
[Datein] [char](8) NULL,
[Timein] [char](6) NULL,
[Coding] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
it's better to run a trigger on this table to distribute record in other table ?
i want a way for good performance
[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
2- +1
10 Comments
Get away from triggers, they kills performance. use stored procedure to transfer records other option is SSIS package.
You insert data into CGI_SMS_d tables, later you push everything into CGI__SMS table, correct? Is this process a scheduled one? If this is a scheduled one you should create a job to run at specific time.
Please correct me if my understandings are wrong.
SSIS Tutorials
Please correct me if my understandings are wrong.
SSIS Tutorials
the main table is cgi_sms_received and i have many cgi_sms_dtable referring to the value founded of the reqport in cgi_sms_received
distribute rows records from cgi_sms_received to other CGI_SMS_d742268
CGI_SMS_d752265 , CGI_SMS_d752266 depending on the value of reqport in cgi_sms_received
distribute rows records from cgi_sms_received to other CGI_SMS_d742268
CGI_SMS_d752265 , CGI_SMS_d752266 depending on the value of reqport in cgi_sms_received
I would not recommend trigger if this is highly transactional table, you can implement this login in the same stored procedure.
Active today
I tried to read all the comments above, but I may have missed something; therefore, I apologize if any of this is duplicated information.
My understanding of the process is as follows.
(1) INSERT data to table A
(2) EXEC stored procedure to distribute data to tables B, C, D, et cetera.
(3) DELETE original record from table A
In my humble opinion, this is a perfect scenario for a trigger; however, I would scrap table A unless you are using it for other purposes. Instead, I would create a view A that presents the data from tables B, C, and D. On this view, create an INSTEAD OF INSERT trigger. Hence, users will interact with the view as they did table A, except that INSERT statements will now process directly to the real physical tables B, C, and D without extra processing later.
I hope that makes sense.
MSDN: http://msdn.microsoft.com/en-us/library/ms189799.aspx
Best regards and happy coding,
Kevin
My understanding of the process is as follows.
(1) INSERT data to table A
(2) EXEC stored procedure to distribute data to tables B, C, D, et cetera.
(3) DELETE original record from table A
In my humble opinion, this is a perfect scenario for a trigger; however, I would scrap table A unless you are using it for other purposes. Instead, I would create a view A that presents the data from tables B, C, and D. On this view, create an INSTEAD OF INSERT trigger. Hence, users will interact with the view as they did table A, except that INSERT statements will now process directly to the real physical tables B, C, and D without extra processing later.
I hope that makes sense.
MSDN: http://msdn.microsoft.com/en-us/library/ms189799.aspx
Best regards and happy coding,
Kevin
i execute a stored procedure from windows application, it's better to run a trigger on insert on the main table?
Active today
Based on my limited knowledge of what you are doing with the application, I would have to say "yes." The reason is that you are constantly filling a table only to delete the data. This seems like a waste in SQL and I/O resources, including the transaction logging for the DELETEs if this process is not a full TRUNCATE. Therefore, I would think it is more efficient from a business process standpoint to INSERT to the appropriate normalized tables. The view offers security if one does not want the users touching the base tables directly. If the users only SELECT from or INSERT to the view, you can control the information both in and out. In other words, an INSERT can be dropped if it does not meet certain criteria. In the current scenario, the errant record makes it to the staging table only to be thrown away. If the reason to do so is auditing (logging of the errors), you can have the INSTEAD OF TRIGGER perform INSERT either to production tables or to logging tables.
To streamline the process, you could look into service broker to have SQL process the INSERTs asynchronously. Again, not sure the full scope of what you are trying to accomplish, but I hope these thoughts help.
To streamline the process, you could look into service broker to have SQL process the INSERTs asynchronously. Again, not sure the full scope of what you are trying to accomplish, but I hope these thoughts help.
Featured Post
Are you interested in becoming an AWS Certified Solutions Architect?
Discover a new interactive way of training for the exam.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse
While installing Eclipse in windows, got one error like above and unable to proceed with the installation.
This video describes how to successfully install Eclipse.
How to solve incompa…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll
730 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.