
/*------ ATD 386986 ------*/
/
DECLARE v_count NUMBER:=0;
BEGIN
SELECT 1 INTO v_count FROM USER_TABLES WHERE UPPER(table_name) = 'PreVenda' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE PreVenda(
Id Int NOT NULL,
Descricao VARCHAR(100) NOT NULL,
CodigoVendedor VARCHAR(5) NULL,
DataCriacao DATETIME NOT NULL,
NumPed INT NULL)';
EXECUTE IMMEDIATE 'alter table PreVenda ADD CONSTRAINT PK_PreVenda PRIMARY KEY (Id)'; 
EXECUTE IMMEDIATE 'CREATE SEQUENCE PreVenda_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER PreVenda_I
BEFORE INSERT ON PreVenda for each ROW
WHEN (new.Id IS NULL)
BEGIN
SELECT PreVenda_SEQ.NEXTVAL INTO :new.Id FROM dual;
END;';
END;
END;
/
DECLARE v_count NUMBER:=0;
BEGIN
SELECT 1 INTO v_count FROM USER_TABLES WHERE UPPER(table_name) = 'ItemPreVenda' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ItemPreVenda(
Id Int NOT NULL,
IdPreVenda Int NOT NULL,
CodigoProduto Varchar(5) NOT NULL,
Quantidade NUMBER(13,2) NOT NULL)';
EXECUTE IMMEDIATE 'alter table ItemPreVenda ADD CONSTRAINT PK_ItemPreVenda PRIMARY KEY (Id)'; 
EXECUTE IMMEDIATE 'alter table ItemPreVenda ADD CONSTRAINT FK_ItemPreVenda FOREIGN KEY (IdPreVenda) REFERENCES PreVenda(Id)';
EXECUTE IMMEDIATE 'CREATE SEQUENCE ItemPreVenda_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER ItemPreVenda_I
BEFORE INSERT ON ItemPreVenda for each ROW
WHEN (new.Id IS NULL)
BEGIN
SELECT ItemPreVenda_SEQ.NEXTVAL INTO :new.Id FROM dual;
END;';
END;
END;
/