Solved

trigger on insert to decompose record in different table refering to reqport

Posted on 2013-01-03
10
1 solution
391 Views
Last Modified: 2013-01-24
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 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

Open in new window



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

Open in new window



it's better to run a trigger on this table to distribute record in other  table ?
i want a way for good performance
0
Comment
Question by:afifosh
[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
Join The Community
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 38740343
Get away from triggers, they kills performance. use stored procedure to transfer records other option is SSIS package.
0
 
LVL 1

Author Comment

by:afifosh
ID: 38740434
can you give me simple tutorial.. about ssis package and how i can implement it
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38740469
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
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Download the report
 
LVL 1

Author Comment

by:afifosh
ID: 38740505
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
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38740538
I would not recommend trigger if this is highly transactional table, you can implement this login in the same stored procedure.
0
 
LVL 1

Author Comment

by:afifosh
ID: 38740547
the already procedure do the same job i am trying to find anothr function or method to do same job
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 38740687
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
0
 
LVL 1

Author Comment

by:afifosh
ID: 38740720
i execute a stored procedure from windows application, it's better to run a trigger on insert on the main table?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 38741054
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.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38743623
Use Instead of trigger and check for correct location (Table) and move data to that part.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

30 Day Free Trial
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Lone Date Ranger :: there is just one right way
Article by: PortletPaul
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Batch File to Perform Backup of MSSQL Database
Article by: Brian
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Phishing Awareness Training
Video by: Dermot
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Enumerating Prime Numbers in Excel / VBA
Video by: Ed
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Advertise Here

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

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

Join & Ask a Question

Advertise Here