Solved

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

Posted on 2013-01-03
10
376 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup 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: 38740547
the already procedure do the same job i am trying to find anothr function or method to do same job
0
 
LVL 59

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 59

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now