--
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 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.
--


/*

Conversion functions: CAST, CONVERT and STR

CAST:
  CAST (original_Expression AS desired_datatype)
  
  for example, to get the username first two letters: 
      CAST(CURRENT_USER AS CHAR(2))
  the same result can be obtained with: SUBSTRING(CURRENT_USER,1,2).
  
CONVERT:
  CONVERT(desired_datatype[(Length)], original_expression [,style])
  
  the third parameter is optional. It is most commonly used with datetime to char convertions.
  
  To obtain the exact date (without time) in ISO format (style 112), use:
  CONVERT(CHAR(10), CURRENT_TIMESTAMP, 112):
     
  
ISO 8601 defines international standard date notation as YYYY-MM-DD HH:MM:SS.MMM.

*/


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


-- ............................................................................
if not exists (select * from dbo.sysobjects 
               where id = object_id(N'[dbo].[TriggerExample]') )
begin
  CREATE TABLE TriggerExample (
	  ProjNum int,
	  Designacao nvarchar (30),
	  Fundos decimal(10,2),
	  
	  Type CHAR(1) Check(type in ('D','E','I','N')),
    UserID VarChar(40),
    EventTime DateTime,
    LogId Integer IDENTITY,
    EventID UNIQUEIDENTIFIER,
     
    User2Char nvarchar(2),
    ExactDate datetime,
    DateWithHour datetime,
    DateWithTime datetime,
    Dateyear int,
    FullDateTime datetime,
     
   
    CONSTRAINT PK_TriggerExample PRIMARY KEY (LogId)
  );    
end

/*
  D - marks a record that was DELETED from Projecto table.
  I - marks a record that was INSERTED from Projecto table.
  E - marks the before version of a Projecto record that was UPDATED.
  N - marks the after version of a Projecto record that was UPDATED.
  
  UserID - records the name of the user who performed the operation.
  EventTime - records the time at which an operation occured.
  LogId - serves to identify each log record.
  EventID - stores event identification.
*/


-- ............................................................................

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

CREATE TRIGGER TR_Insert_Projecto
ON Projecto 
FOR INSERT
AS
BEGIN
	INSERT INTO TriggerExample (ProjNum, Designacao, Fundos,
	      Type, UserID, EventTime,EventId,
	      User2Char, 
	      ExactDate,
	      DateWithHour,
	      DateWithTime,
	      DateYear,
	      FullDateTime)
	Select i.ProjNum, i.Designacao, i.Fundos,
	      'I',CURRENT_USER, CURRENT_TIMESTAMP,NEWID(),
	      CAST(CURRENT_USER AS CHAR(2)),
	      CONVERT(CHAR(10), CURRENT_TIMESTAMP, 120), 
	      CONVERT(CHAR(13), CURRENT_TIMESTAMP, 120)+':00', 
	      CONVERT(CHAR(16), CURRENT_TIMESTAMP, 120),
	      Year(CURRENT_TIMESTAMP), -- or DATEPART(yy, CURRENT_TIMESTAMP),
	      GETDATE()  -- or use the function CURRENT_TIMESTAMP 
	       
	From Inserted I
END
GO

-- ............................................................................


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

CREATE TRIGGER TR_delete_Projecto
ON Projecto 
FOR DELETE
AS
BEGIN
	
	INSERT INTO TriggerExample (ProjNum, Designacao, Fundos,
	      Type, UserID, EventTime,EventId,
	      User2Char, 
	      ExactDate,
	      DateWithHour,
	      DateWithTime,
	      DateYear,
	      FullDateTime)
	Select d.ProjNum, d.Designacao, d.Fundos,
	      'D', CURRENT_USER, CURRENT_TIMESTAMP,NEWID(),
	      SUBSTRING(CURRENT_USER,1,2),
	      CONVERT(CHAR(10), CURRENT_TIMESTAMP, 120), 
	      CONVERT(CHAR(13), CURRENT_TIMESTAMP, 120)+':00', 
	      CONVERT(CHAR(16), CURRENT_TIMESTAMP, 120),
	      Year(CURRENT_TIMESTAMP), -- or DATEPART(yy, CURRENT_TIMESTAMP),
	      GETDATE()  -- or use the function CURRENT_TIMESTAMP 
	       
	From deleted d
END
GO

      
-- ............................................................................


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

CREATE TRIGGER TR_Update_Projecto
ON Projecto 
FOR UPDATE
AS
BEGIN
	
	DECLARE @ProjNum int, @dDesignacao char(30), @dFundos real
	DECLARE @iDesignacao Char(30), @iFundos real
	DECLARE @guid UNIQUEIDENTIFIER
	
	DECLARE UpdateProjectoLog CURSOR
	For 
	Select d.ProjNum, d.Designacao, d.Fundos, i.Designacao, i.Fundos
	From deleted d, inserted i
	Where d.ProjNum = i.ProjNum
	 
	
	-- SEE THIS IF...
	
	IF not UPDATE(Fundos)  -- only if the user try update the "fundos" field...
	  RETURN
	
	
	Open UpdateProjectoLog
	
	Fetch Next From UpdateProjectoLog
	INTO @ProjNum, @dDesignacao, @dFundos, @iDesignacao, @iFundos
	
	While (@@Fetch_Status =0)
	BEGIN 
		Select @guid = NEWID()
	
	  INSERT INTO TriggerExample (ProjNum, Designacao, Fundos,
	      Type, UserID, EventTime,EventId,
	      User2Char, 
	      ExactDate,
	      DateWithHour,
	      DateWithTime,
	      DateYear,
	      FullDateTime)
	  Values(@ProjNum, @dDesignacao, @dFundos,
	      'E', CURRENT_USER, CURRENT_TIMESTAMP,@guid,
	      SUBSTRING(CURRENT_USER,1,2),
	      CONVERT(CHAR(10), CURRENT_TIMESTAMP, 120), 
	      CONVERT(CHAR(13), CURRENT_TIMESTAMP, 120)+':00', 
	      CONVERT(CHAR(16), CURRENT_TIMESTAMP, 120),
	      Year(CURRENT_TIMESTAMP), 
	      GETDATE())

	  INSERT INTO TriggerExample (ProjNum, Designacao, Fundos,
	      Type, UserID, EventTime,EventId,
	      User2Char, 
	      ExactDate,
	      DateWithHour,
	      DateWithTime,
	      DateYear,
	      FullDateTime)
	  Values(@ProjNum, @iDesignacao, @iFundos,
	      'N', CURRENT_USER, CURRENT_TIMESTAMP,@guid,
	      SUBSTRING(CURRENT_USER,1,2),
	      CONVERT(CHAR(10), CURRENT_TIMESTAMP, 120), 
	      CONVERT(CHAR(13), CURRENT_TIMESTAMP, 120)+':00', 
	      CONVERT(CHAR(16), CURRENT_TIMESTAMP, 120),
	      Year(CURRENT_TIMESTAMP), 
	      GETDATE())	      

	  Fetch Next From UpdateProjectoLog
	  INTO @ProjNum, @dDesignacao, @dFundos, @iDesignacao, @iFundos
  END
  
  CLOSE UpdateProjectoLog
  DEALLOCATE UpdateProjectoLog
END
GO
-- ............................................................................