Solved

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

Posted on 2013-01-03
10
394 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
  • 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
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.

 
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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 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