IF NOT EXISTS (SELECT * FROM SYS.OBJECTS where OBJECT_NAME(OBJECT_ID)= 'EST_PROD_CPL_CARGA')
BEGIN 
	CREATE TABLE [DBO].[EST_PROD_CPL_CARGA](
		[CD_CTR] [INT] IDENTITY(1,1) NOT NULL,
		[CD_EMP] [INT] NULL,
		[CD_FILIAL] [INT] NULL,
		[CD_PROD] [INT] NULL,
		[TP_REG] [TINYINT] NULL,
		[DT_HR] [DATETIME] NULL,
		[VERSAO]  INT DEFAULT  datediff(second, '1970-01-01T00:00:00', GETDATE()),
	 CONSTRAINT [PK_EST_PROD_CPL_CARGA] PRIMARY KEY CLUSTERED 
		([CD_CTR] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
 
	
END ;
GO
/*
BEGIN TRY 
CREATE NONCLUSTERED INDEX IX_EST_PROD_CPL_CARGA_VERSAO ON EST_PROD_CPL_CARGA
	(VERSAO) WITH (FILLFACTOR=97);
END TRY 
BEGIN CATCH
	 Print 'Nao criou index '
END CATCH

GO
IF EXISTS( SELECT * FROM SYS.OBJECTS 
			WHERE NAME ='TRG_EST_PROD_CPL_CARGA' AND TYPE = 'TR')
BEGIN
	DROP TRIGGER dbo.TRG_EST_PROD_CPL_CARGA
END
GO
GO 
CREATE TRIGGER [dbo].[TRG_EST_PROD_CPL_CARGA] ON [dbo].[EST_PROD_CPL] 
FOR 
	INSERT, UPDATE, DELETE
AS 
SET NOCOUNT ON
DECLARE @tpReg as tinyint=2;

if Exists(select * from Inserted)
	Set @tpReg =0;

if Exists(select * from Deleted)
	if @tpReg =0
		Set @tpReg =1;


if @tpReg =2
	begin
		INSERT INTO EST_PROD_CPL_CARGA (CD_EMP,CD_FILIAL,CD_PROD,TP_REG,DT_HR)
		SELECT
			 CD_EMP
			,CD_FILIAL
			,CD_PROD
			,@tpReg TP_REG
			,GETDATE() DT_HR
		FROM DELETED
	END
else
	BEGIN	
		INSERT INTO EST_PROD_CPL_CARGA (CD_EMP,CD_FILIAL,CD_PROD,TP_REG,DT_HR)
		SELECT
			 CD_EMP
			,CD_FILIAL
			,CD_PROD
			,@tpReg TP_REG
			,GETDATE() DT_HR
		FROM INSERTED
	END
	*/