-- ---------------------------------------------------------------------------
-- --------------------------------------  ======   ===     ===  -------------
-- Um caso de estudo. -------------------   ==   ==  ==  =  ==   -------------
-- Um Data Warehouse para a Northwind. --   ==   ==  == === ==   -------------
-- --------------------------------------  ======     === ===    -------------

-- Criac‹o de gatilhos para CDC sobre as tabelas base das dimens›es no sistema 
-- operacional. 

-- Defini¨‹o do gatilho para a captura das modifica¨›es
-- efetuadas sobre os dados contidos na tabela "Customers"
/* Categorias */

create or replace TRIGGER trg_Categorias
AFTER INSERT OR UPDATE OR DELETE ON CATEGORIES
FOR EACH ROW
BEGIN
 if inserting then
   insert into AUDDIM_CATEGORIA(ID,DESIGNACAO,OPERACAO) VALUES (:new.CATEGORYID,:new.DESCRIPTION,'I');
 end if;
 if deleting then
   insert into AUDDIM_CATEGORIA(ID,DESIGNACAO,OPERACAO) VALUES (:old.CATEGORYID,:old.DESCRIPTION,'D');
 end if;
 if updating then
   insert into AUDDIM_CATEGORIA(ID,DESIGNACAO,OPERACAO) VALUES (:new.CATEGORYID,:new.DESCRIPTION,'U');
 end if;

END;
/
/* Clientes */

create or replace TRIGGER trg_Cliente
AFTER INSERT OR UPDATE OR DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
 if inserting then
   insert into AUDDIM_CLIENTE(ID,DESIGNACAO,TELEFONE,CIDADE,PAIS,OPERACAO) VALUES (:new.CUSTOMERID,:new.COMPANYNAME,:new.PHONE,:new.CITY,:new.COUNTRY,'I');
 end if;
 if deleting then
   insert into AUDDIM_CLIENTE(ID,DESIGNACAO,TELEFONE,CIDADE,PAIS,OPERACAO) VALUES (:new.CUSTOMERID,:new.COMPANYNAME,:new.PHONE,:new.CITY,:new.COUNTRY,'D');
 end if;
 if updating then
   insert into AUDDIM_CLIENTE(ID,DESIGNACAO,TELEFONE,CIDADE,PAIS,OPERACAO) VALUES (:new.CUSTOMERID,:new.COMPANYNAME,:new.PHONE,:new.CITY,:new.COUNTRY,'U');
 end if;

END;
/
/* Funcionario */

create or replace TRIGGER trg_Funcionario
AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEES
FOR EACH ROW
BEGIN
 if inserting then
   insert into AUDDIM_FUNCIONARIO(ID,NOME,ENDERECO,CIDADE,REGIAO,PAIS,OPERACAO) VALUES (:new.EMPLOYEEID,:new.FIRSTNAME || :new.LASTNAME,:new.ADDRESS,:new.CITY,:new.REGION,:new.COUNTRY,'I');
 end if;
 if deleting then
   insert into AUDDIM_FUNCIONARIO(ID,NOME,ENDERECO,CIDADE,REGIAO,PAIS,OPERACAO) VALUES (:new.EMPLOYEEID,:new.FIRSTNAME || :new.LASTNAME,:new.ADDRESS,:new.CITY,:new.REGION,:new.COUNTRY,'D');
 end if;
 if updating then
   insert into AUDDIM_FUNCIONARIO(ID,NOME,ENDERECO,CIDADE,REGIAO,PAIS,OPERACAO) VALUES (:new.EMPLOYEEID,:new.FIRSTNAME || :new.LASTNAME,:new.ADDRESS,:new.CITY,:new.REGION,:new.COUNTRY,'U');
 end if;

END;
/
/* PRODUTO */

create or replace TRIGGER trg_Produto
AFTER INSERT OR UPDATE OR DELETE ON PRODUCTS
FOR EACH ROW
BEGIN
 if inserting then
   insert into AUDDIM_PRODUTO(ID,DESIGNACAO,CATEGORIA,OPERACAO) VALUES (:new.PRODUCTID,:new.PRODUCTNAME, :new.CATEGORYID,'I');
 end if;
 if deleting then
   insert into AUDDIM_PRODUTO(ID,DESIGNACAO,CATEGORIA,OPERACAO) VALUES (:new.PRODUCTID,:new.PRODUCTNAME, :new.CATEGORYID,'D');
 end if;
 if updating then
   insert into AUDDIM_PRODUTO(ID,DESIGNACAO,CATEGORIA,OPERACAO) VALUES (:new.PRODUCTID,:new.PRODUCTNAME, :new.CATEGORYID,'U');
 end if;

END;
/
create or replace trigger audTFEncomendas
after insert on order_details
for each row
begin
  INSERT INTO AUDTF_Encomendas (FONTE, ENCOMENDA, DATAENCOMENDA,
  FUNCIONARIO, CLIENTE, PRODUTO, QUANTIDADE, PRECO, VALORTOTAL, VALORDESCONTO,
  VALORTOTALCOMDESCONTO)
	SELECT 1, Nord.OrderID, Nord.OrderDate, Nord.EmployeeID, Nord.CustomerID, :new.ProductId, 
		:new.Quantity, :new.UnitPrice, :new.Quantity*:new.UnitPrice AS TotalValue,
		:new.Quantity*:new.UnitPrice*:new.Discount AS DiscountValue,
		(:new.Quantity*:new.UnitPrice)-(:new.Quantity*:new.UnitPrice*:new.Discount) AS TotalWDiscountValue
		FROM Northwind.Orders Nord where Nord.OrderId = :new.OrderId;
end;
/
create or replace trigger audTFQualidadeServico
after insert or update of requireddate,shippeddate  on orders
for each row
WHEN (new.requireddate is not null and  new.shippeddate is not null)
begin
  INSERT INTO AudTF_QualidadeServico (ENCOMENDA, DATAENCOMENDA, DATAPARAENTREGAR, DATADESPACHO, FUNCIONARIO, CLIENTE, DIASATEPEDIDOENTREGA, DIASATEDESPACHO, INDICERELACAO)
	values (:new.OrderID, :new.OrderDate, :new.RequiredDate, :new.ShippedDate, :new.EmployeeID, :new.CustomerID,
trunc(:new.OrderDate-:new.RequiredDate),
trunc(:new.OrderDate-:new.ShippedDate),
		CASE WHEN  (:new.OrderDate-:new.RequiredDate) - (:new.OrderDate-:new.ShippedDate) < 0
			THEN -1
			ELSE 1
		END);
end;
/