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
USE [CGI_SMS_SERVICES]GO/****** Object: StoredProcedure [dbo].[CGI_SMS_Services_MainTrigger] Script Date: 01/03/2013 18:00:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER 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
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
8/22/2022 - Mon
Anuj
Get away from triggers, they kills performance. use stored procedure to transfer records other option is SSIS package.
AFIF JABBADO
ASKER
can you give me simple tutorial.. about ssis package and how i can implement it
Anuj
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.
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
Anuj
I would not recommend trigger if this is highly transactional table, you can implement this login in the same stored procedure.
AFIF JABBADO
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.