-- INSTEAD OF TRIGGER
-- There can only be One INSTEAD OF trigger
-- per table
CREATE TRIGGER ContactTypeDenyInsertTrigger
    ON [Person].[ContactType] 
Instead Of Insert
AS 
    PRINT 'I in`t gonna insert that.' 
GO

-- Execution
INSERT [Person].[ContactType] (Name,ModifiedDate)
VALUES ('Assassin',GETDATE());

-- Output
-- I in`t gonna insert that.
-- (1 row(s) affected)

SELECT
    [so].[name] AS [trigger_name],
    USER_NAME([so].[uid]) AS [trigger_owner],
    USER_NAME([so2].[uid]) AS [table_schema],
    OBJECT_NAME([so].[parent_obj]) AS [table_name],
    OBJECTPROPERTY( [so].[id], 
              'ExecIsUpdateTrigger') AS [IsUpdate],
    OBJECTPROPERTY( [so].[id],  
              'ExecIsDeleteTrigger') AS [IsDelete],
    OBJECTPROPERTY( [so].[id], 
              'ExecIsInsertTrigger') AS [IsInsert],
    OBJECTPROPERTY( [so].[id],
              'ExecIsAfterTrigger') AS [IsAfter],
    OBJECTPROPERTY( [so].[id],
              'ExecIsInsteadOfTrigger') AS [IsInsteadof],
    CASE OBJECTPROPERTY([so].[id], 
              'ExecIsTriggerDisabled')
          WHEN 1 THEN 'Disabled'
          ELSE 'Enabled'
        END AS status
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so].[type] = 'TR'

-- SOURCE: https://stackoverflow.com/a/4307260

-- After Insert trigger
ALTER TRIGGER [Person].[ContactTypeTrigger]
      ON [Person].[ContactType] 
AFTER Insert, Update
AS 
   IF ('Batman' = (SELECT  I.Name FROM inserted I))
        BEGIN
	  RAISERROR('You can`t add Batman.',16,1);
	  ROLLBACK TRANSACTION;
	END

   IF Update(Name)
     SELECT  'Name was modified from ' 
           + D.Name 
           + ' to '
           + I.Name 
      FROM Inserted I
	  JOIN Deleted D
	  ON I.ContactTypeID = D.ContactTypeID;;
   
GO

-- Execution 1 (Success)
Update Person.ContactType 
   SET Name = 'Contractor' WHERE Name = 'Sales Manager'
-- [Output]
-- Name was modified from Sales Manager to Contractor

-- Execution 2 (Fails)
Update Person.ContactType 
   SET Name = 'Batman' WHERE Name = 'Contractor'
-- [Output]
-- Msg 50000,...
-- You can`t add Batman.

-- DML TRIGGER
-- Trigger on an INSERT, UPDATE, or DELETE
CREATE|ALTER TRIGGER [schema_name.]trigger_name   
ON table|view
   [WITH ENCRYPTION|SCHEMABINDING|EXECUTE AS Clause]  
   FOR|AFTER|INSTEAD OF
   INSERT[,]|UPDATE[,]|DELETE
   [WITH APPEND]  
   [NOT FOR REPLICATION]   
AS 
  sql_statement  [ ; ] [ ,...n ] | 
  EXTERNAL NAME assembly_name.class_name.method_name 
  
-- DDL TRIGGER
-- Trigger on a CREATE, ALTER, DROP, GRANT,
-- DENY, REVOKE or UPDATE 
CREATE|ALTER TRIGGER trigger_name   
ON ALL SERVER | DATABASE 
   [WITH ENCRYPTION|EXECUTE AS Clause]  
   FOR|AFTER
   event_type|event_group
   [ ,...n ]  
AS 
  sql_statement  [ ; ] [ ,...n ] | 
  EXTERNAL NAME assembly_name.class_name.method_name 
  
-- LOGON TRIGGER
CREATE|ALTER TRIGGER trigger_name   
ON ALL SERVER   
   [WITH ENCRYPTION|EXECUTE AS Clause]  
   FOR|AFTER 
   LOGON    
AS
   sql_statement [ ; ] [ ,...n ] |
   EXTERNAL NAME assembly_name.class_name.method_name 

BEGIN TRAN; 
  BEGIN TRY;
	  INSERT Table1 (Col1, Col2) 
		    VALUES(123, 100);
	  INSERT Table2 (Col1, Col2) 
		    VALUES( 123, -100);
	  COMMIT TRAN;
  END TRY
  BEGIN CATCH
    ROLLBACK TRAN;
    RAISERROR('Transaction failed.', 14, 1);
  END CATCH;

-- Add New message
EXEC sp_addmessage 50001, 16,
    'Unable to update %s';

-- Update Message
EXEC sp_addmessage 50001, 16, 
    'Still unable to update %s',
    @Replace = 'Replace';

SELECT * 
  FROM sys.messages
  WHERE message_id > 50000;
  
-- Generate messages script
SELECT 'EXEC sp_addmessage ' 
  + Cast(message_id as VARCHAR(7)) 
  + ', ' + Cast(Severity as VARCHAR(2)) 
  + ', ''' + [text] +  ''';' 
FROM sys.messages
WHERE message_id > 50000;

-- Delete Message
EXEC sp_dropmessage 50001

-- Severity levels from 20 through 25 are 
-- considered fatal. If a fatal severity level 
-- is encountered, the client connection is terminated
-- after receiving the message, and the error is
-- logged in the error and application logs.

RAISERROR('Print', 10,1);
RAISERROR('Info', 14,1);
RAISERROR('Warning', 15,1);
RAISERROR('Critical', 16,1);

RAISERROR (15600,-1,-1, 'mysp_CreateCustomer');  
-- Msg 15600, Level 15, State 1, Line 9
-- An invalid parameter or option was specified 
-- for procedure 'mysp_CreateCustomer'.

RAISERROR('Raise a custom error', 12, 1) ;
-- Msg 50000, Level 12, State 1, Line 15
-- Raise a custom error

RAISERROR (N'This is message %s %d.', -- Message text.  
           10, -- Severity,  
           1, -- State,  
           N'number', -- First argument.  
           5);
-- This is message number 5.


RAISERROR (N'<\<%*.*s>>', -- Message text.  
           10, -- Severity,  
           1, -- State,  
           15, -- First argument used for width.  
           3, -- Second argument used for precision.  
           N'abcdefghi');

-- With Log
-- Logs can be viewed at :
-- SQL Server > Object Explorer >
--    Management > SQL Server Logs > Current
RAISERROR('Print', 10,1) WITH LOG;
		

BEGIN TRY;
  SET NOCOUNT ON; 
  SELECT 'Some sql statement ...';
  RAISERROR('Raise a custom error', 16, 1) ;
  Select 'This code wont execute';
END TRY 

BEGIN CATCH 
  SELECT 
    ERROR_MESSAGE() AS [Message],
    ERROR_PROCEDURE() AS [Procedure],
    ERROR_LINE() AS Line,
    ERROR_NUMBER() AS Number,
    ERROR_SEVERITY() AS Severity,
    ERROR_STATE() AS State;
END CATCH;
SELECT 'The end';


-- Traditional error handling 
DECLARE @ErrorCode INT;

PRINT 'Some error prone sql statement.'
SET @ErrorCode= @@ERROR;

IF @ErrorCode <> 0
BEGIN
  -- ERROR HANDLING CODE GOES HERE
	PRINT @ErrorCode
END

DECLARE my_cursor CURSOR  
    FAST_FORWARD READ_ONLY 
    FOR SELECT Name 
            FROM Production.Product
	
OPEN my_cursor   

DECLARE @Name Nvarchar(50);
FETCH my_cursor INTO @Name

SELECT @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @Name
   FETCH my_cursor INTO @Name
   --PRINT @Name
   --FETCH NEXT FROM my_cursor;
END
CLOSE my_cursor 

DEALLOCATE my_cursor

[CREATE|ALTER]  [PROC|PROCEDURE]
    [schema_name.]procedure_name [@parameter data_type] 
    [NULL|NOT NULL ] [ = default ] 
    [OUT|OUTPUT| [READONLY]]
  [WITH {procedure_option}, ...]
  [FOR REPLICATION]
AS
[BEGIN]
  sql_statement;
[END]