asked on trigger on insert to decompose record in different table refering to reqport
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
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
Last Comment
Alpesh Patel
Get away from triggers, they kills performance. use stored procedure to transfer records other option is SSIS package.
ASKER
can you give me simple tutorial.. about ssis package and how i can implement it
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
ASKER
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.
ASKER
the already procedure do the same job i am trying to find anothr function or method to do same job
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
ASKER
i execute a stored procedure from windows application, it's better to run a trigger on insert on the main table?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Use Instead of trigger and check for correct location (Table) and move data to that part.