--
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Criar mais tabelas para ilustrar o funcionamento dos triggers
-- (create more database tables to ilustrate how triggers work)
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

-- Using the inserted and deleted Tables
-- Two special tables are used in trigger statements: the deleted table and the
-- inserted table. Microsoft SQL Server 2000 automatically creates and
-- manages these tables. You can use these temporary, memory-resident tables to
-- test the effects of certain data modifications and to set conditions for
-- trigger actions; however, you cannot alter the data in the tables directly.

-- The 'deleted' table stores copies of the affected rows during DELETE and
-- UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are
--  deleted from the trigger table and transferred to the deleted table.
--  The deleted table and the trigger table ordinarily have no rows in common.

-- ............................................................................
--  The 'inserted' table stores copies of the affected rows during INSERT and
--  UPDATE statements. During an insert or update transaction, new rows are
--  added simultaneously to both the inserted table and the trigger table.
--  The rows in the inserted table are copies of the new rows in the trigger
--  table.
--


-------------------------------------------------------------------------------
-- USE DBProject: Changes the database context to the DBProject database.
--
USE DBProject
GO


-- ............................................................................
if not exists (select * from dbo.sysobjects
               where id = object_id(N'[dbo].[Financiador]') )
begin
  CREATE TABLE Financiador (
          FinNum int NOT NULL
             CHECK (FinNum >= 1),                    -- constraint type: check
    Nome nvarchar(30) NOT NULL,
   
    CONSTRAINT PK_FinNum PRIMARY KEY (FinNum) -- constraint type: primary key
  );   
end


-- ............................................................................
if not exists (select * from sysobjects
               where id = object_id(N'[dbo].[Financiador_Historico]') )
begin
  CREATE TABLE Financiador_Historico (
    NumReg int NOT NULL,
          FinNum int NOT NULL,
    Nome nvarchar(30) NOT NULL,
    DAlteracao datetime NOT NULL,
   
    CONSTRAINT PK_NumReg PRIMARY KEY (NumReg) -- constraint type: primary key
  );   
end


--  Criar um trigger INSERT && UPDATE, usa a pseudotabela 'Inserted'.
--  Purpose: Generate a unique NumReg.
-- ............................................................................

if exists (select name from sysobjects
               where name = 'TR_Financiador' AND type = 'TR' )
  DROP TRIGGER TR_Financiador
GO

CREATE TRIGGER TR_Financiador
ON Financiador
FOR INSERT, UPDATE
AS
--    SET NOCOUNT ON
 
--  Vari?veis locais   
    DECLARE @NumReg int
    DECLARE @FinNum int
    DECLARE @Nome  nvarchar(30)
   
    DECLARE @MAX_TRY int
    DECLARE @NTry int
   
    IF (@@ROWCOUNT = 0)  -- if no rows affected, just return
      RETURN
   
    SET @MAX_TRY = 20    -- Max number of times to try
   
--
-- with a cursor we can iterate the 'inserted' rows
--
   
    DECLARE fh_cursor CURSOR FOR               -- fh_cursor: fh for Financiador_Historico
    SELECT FinNum, Nome FROM inserted ORDER by FinNum
   
    OPEN fh_cursor

    FETCH NEXT FROM fh_cursor INTO @FinNum, @Nome
 
    WHILE (@@fetch_status = 0)
      BEGIN
        SET @NTry = 1     
     
        -- try insert the row in Financiador_Historico table
        WHILE (@NTry <= @MAX_TRY)
          BEGIN 
            Set @NTry = @NTry +1   
     
            -- get next NumReg
            Select @NumReg = Max(NumReg) From Financiador_Historico     

            IF (@NumReg IS NULL)
              SET @NumReg = 1
            ELSE
              SET @NumReg = @NumReg +1

            INSERT INTO Financiador_Historico Values (@NumReg, @FinNum, @Nome,GETDATE())

            IF (@@ERROR = 0)    -- Success!
              BREAK
          END
    
        IF ( @NTry > @MAX_TRY)
          GOTO on_error

        FETCH NEXT FROM fh_cursor INTO @FinNum, @Nome
      END
 
    IF  (@@fetch_status = -1)
      Begin
        CLOSE fh_cursor
        DEALLOCATE fh_cursor
        RETURN
      End
 
    on_error:
     CLOSE fh_cursor
     DEALLOCATE fh_cursor

 
      RAISERROR('Can''t insert the record',16,1 )
      ROLLBACK TRANSACTION
GO      
-- ............................................................................

if not exists (select * from dbo.sysobjects
               where id = object_id(N'[dbo].[FinProj]') )
begin

  CREATE TABLE FinProj (
          FinNum int NOT NULL,                       -- financiador
          ProjNum int NOT NULL,                      -- projecto financiado
    DInicioFin smalldatetime                   -- data de inicio do financiamento
       DEFAULT GETDATE(),
    DRegisto datetime,                         -- data de entrada do registo
   
    CONSTRAINT PK_FinProj PRIMARY KEY (FinNum, ProjNum)
  );   
end
GO

-- ............................................................................
if  exists (select name from sysobjects
               where name = 'TR_FinProj' AND type = 'TR' )
  DROP TRIGGER TR_FinProj
GO

CREATE TRIGGER TR_FinProj
on FinProj AFTER INSERT
AS
  SET DATEFORMAT ymd
 
  --Existem NULLS ?
  IF Exists (Select * From Inserted where DRegisto IS NULL)
    Update FinProj
      Set DRegisto = GETDATE(),
          DInicioFin = '2005-05-30'
    Where FinNum = (Select FinNum From Inserted)
      and ProjNum = (Select ProjNum From Inserted)
      and  DRegisto IS NULL

GO