-------------------------------------------------------------
-- TRIGGERS
-------------------------------------------------------------

-- * A Trigger is a database object that is attached to a table. 
--  In many aspects it is similar to a stored procedure. 
--  As a matter of --  fact, triggers are often referred to as a "special kind of stored procedure." 
--  The main difference between a trigger and a stored procedure is that the former is attached to a 
--  table and is only fired when an INSERT, UPDATE or DELETE occurs. 
--  You specify the modification action(s) that fire the trigger when it is created.

-------------------------------------------------------------
-- Basic AFTER INSERT Trigger
-------------------------------------------------------------

USE NorthWind
GO

-- * The following shows how to create a trigger that displays the current system time when a row is 
--   inserted into the table to which it is attached.

Drop TABLE my_tab

CREATE TABLE my_tab
(Sou_ID int IDENTITY, 
Sou_Desc varchar(10))

Drop TRIGGER tr_mytab_INSERT

CREATE TRIGGER tr_mytab_INSERT
ON my_tab
AFTER INSERT
AS
PRINT GETDATE()


INSERT my_tab(Sou_Desc) VALUES ('Test 1')

-- * A trigger that inserts values into log table after successfull INSERT Operation 

DROP TABLE log_trigg_tbl
GO

CREATE TABLE log_trigg_tbl
(dml_id   INT IDENTITY PRIMARY KEY,
 dml_comm CHAR(6) NOT NULL,
 dml_time DATETIME DEFAULT GETDATE() NOT NULL,
 dml_user VARCHAR(20) DEFAULT USER)
 GO

SELECT * FROM log_trigg_tbl

---creating trigger for auditing
DROP TRIGGER log_trigg

CREATE TRIGGER log_trigg
ON categories
AFTER INSERT
AS
INSERT INTO log_trigg_tbl
VALUES ( 'INSERT', GETDATE(), USER)

---Insert command for trigger

INSERT INTO categories
VALUES ('organic', 'All kindes of organic products', NULL)

SELECT * FROM categories

SELECT * FROM log_trigg_tbl

-------------------------------------------------------------
-- INSERTED and DELETED Virtual Tables 
-------------------------------------------------------------

-------------------------------------------------------------
-- DELETED 1, Log delete action 
-------------------------------------------------------------

DROP TABLE tab
DROP TABLE tab_log

SELECT lastname, firstname
INTO tab
FROM employees

SELECT lastname, firstname
INTO tab_log1
FROM employees
WHERE 1=2

ALTER TABLE tab_log 
ADD action_id varchar(3)

SELECT * FROM tab
SELECT * FROM tab_log

CREATE TRIGGER del_tab_trig
ON tab
AFTER delete
AS
	INSERT INTO tab_log
	SELECT *, 'DEL' FROM deleted   

DELETE FROM tab
WHERE lastname LIKE '%a%'

SELECT * FROM tab
SELECT * FROM tab_log

-------------------------------------------------------------
-- DELETED 2, Pervent Delete Action 
-------------------------------------------------------------

ALTER TRIGGER del_tab_trig
ON tab
AFTER delete
AS
	RAISERROR('This table cannot be deleted !' , 16 , 1) 

DELETE FROM tab
WHERE lastname LIKE '%a%'

SELECT * FROM tab
SELECT * FROM tab_log

-------------------------------------------------------------
-- DELETED 2, Pervent & Log Delete Action 
-------------------------------------------------------------

CREATE TABLE warnings 
(warning varchar(25) )

ALTER TRIGGER del_tab_trig
ON tab
AFTER delete
AS
	BEGIN 
	INSERT INTO warnings VALUES ('warning !')
	RAISERROR('This table cannot be deleted !' , 16 , 1)
	END  

DELETE FROM tab
WHERE lastname LIKE '%a%'

SELECT * FROM warnings 

SELECT * FROM tab
SELECT * FROM tab_log



-------------------------------------------------------------
-- INSERTED 1
-------------------------------------------------------------

Drop TRIGGER ins_tab_trig

CREATE TRIGGER ins_tab_trig
ON tab
AFTER insert 
AS
	INSERT INTO tab_log
	SELECT *, 'INS' 
	FROM inserted   

INSERT INTO  tab 
VALUES ('Moshe' , 'Levi') , ('Haim' , 'Cohen')

SELECT * 
FROM tab

SELECT * 
FROM tab_log

-------------------------------------------------------------
-- INSERTED 2
-------------------------------------------------------------

Drop TABLE InetLog

CREATE TABLE InetLog 
(Clientname varchar(25), 
 Area       varchar(50), 
 LogTime datetime)


Drop TABLE LogSummary

CREATE TABLE LogSummary 
(Area varchar(50), LogSum_Count int)


INSERT LogSummary VALUES ('Tel Aviv',0)
INSERT LogSummary VALUES ('Jerusalem',0)
INSERT LogSummary VALUES ('Haifa',0)

CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
AFTER INSERT
AS
IF EXISTS (SELECT * 
		   FROM inserted 
		   WHERE area = 'Tel Aviv')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE area = 'Tel Aviv')
  WHERE area = 'Tel Aviv'
 END
IF EXISTS (SELECT * FROM inserted WHERE area = 'Jerusalem')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE area = 'Jerusalem')
  WHERE area = 'Jerusalem'
 END
IF EXISTS (SELECT * FROM inserted WHERE area = 'Haifa')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE area = 'Haifa')
  WHERE area = 'Haifa'
 END

INSERT INTO InetLog VALUES ('A' , 'Tel Aviv' , GETDATE())
INSERT INTO InetLog VALUES ('B' , 'Haifa' , GETDATE())
INSERT INTO InetLog VALUES ('C' , 'Jerusalem' , GETDATE())
INSERT INTO InetLog VALUES ('D' , 'Tel Aviv' , GETDATE())
INSERT INTO InetLog VALUES ('E' , 'Tel Aviv' , GETDATE())
INSERT INTO InetLog VALUES ('F' , 'Jerusalem' , GETDATE())
INSERT INTO InetLog VALUES ('G' , 'Tel Aviv' , GETDATE())
INSERT INTO InetLog VALUES ('H' , 'TJerusalem' , GETDATE())
INSERT INTO InetLog VALUES ('I' , 'Tel Aviv' , GETDATE())
INSERT INTO InetLog VALUES ('J' , 'Jerusalem' , GETDATE())
INSERT INTO InetLog VALUES ('K' , 'Haifa' , GETDATE())

SELECT * FROM InetLog

SELECT * FROM LogSummary


-------------------------------------------------------------
-- UPDATE TRIGGER 
-------------------------------------------------------------

--- Trigger which eliminate the possibility of making changes on the lastname column
DROP TRIGGER emp_id_update

CREATE TRIGGER emp_id_update
ON employees
AFTER UPDATE
AS
	IF UPDATE(lastname)
			RAISERROR ('Transaction cannot be processed.\ *******employee last name cannot be modified',11,1)

---update lastname column

UPDATE employees
SET lastname = 'SEGAL4'
WHERE employeeid = 2


SELECT * FROM employees

-------------------------------------------------------------
-- Instead of
-------------------------------------------------------------

DROP TABLE my_emp

SELECT *, 1 active
INTO my_emp
FROM employees

SELECT * FROM my_emp

DROP TRIGGER del_emp_trig

CREATE TRIGGER del_emp_trig
ON my_emp
INSTEAD OF DELETE
AS
	UPDATE my_emp
	SET active = 0
	FROM my_emp E JOIN Deleted D
	ON E.employeeId = D.EmployeeId

DELETE FROM my_emp
WHERE employeeid = 2

SELECT * FROM my_emp




-------------------------------------------------------------
-- DDL Trigger
-------------------------------------------------------------

CREATE TABLE create_tab_audit
(data xml)

CREATE TRIGGER CreateTable_trigg
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @data XML
SET @data = eventdata()
INSERT INTO create_tab_audit VALUES (@data) 

SELECT * FROM create_tab_audit

CREATE TABLE test1 (id int)

SELECT * FROM create_tab_audit

-------------------------------------------------------------
-- NESTED & RECURSIVE TRIGGERS
-------------------------------------------------------------

DROP TABLE A 
GO
DROP TABLE B 
GO
DROP TABLE C
GO

CREATE TABLE A (value varchar(25))
GO 
CREATE TABLE B (value varchar(25))
GO 
CREATE TABLE C (value varchar(25))
GO 

-- Trigger on A 

CREATE TRIGGER a_trigger
ON a 
AFTER INSERT
AS
BEGIN 
		PRINT 'Table A - Insert action' 
		INSERT INTO b VALUES ('Value by trigger A')
END 
GO

SELECT * FROM a 

INSERT INTO a VALUES ('A')
GO 

SELECT * FROM a 
GO
SELECT * FROM b
GO

-- Trigger on B (Nested)

CREATE TRIGGER a_trig 
ON b 
AFTER INSERT
AS
BEGIN 
		PRINT 'Table B - Insert action' 
		INSERT INTO c VALUES ('Value by trigger B')
END 
GO

SELECT * FROM a 
SELECT * FROM b 

INSERT INTO a VALUES ('A')
GO 

SELECT * FROM a 
GO
SELECT * FROM b
GO
SELECT * FROM c
GO

-- Trigger on C ( Recursive )

CREATE TRIGGER c_trig 
ON c 
AFTER INSERT
AS
BEGIN 
		PRINT 'Table C - Insert action' 
		INSERT INTO a VALUES ('Value by trigger C')
END 
GO

INSERT INTO a VALUES ('A')

SELECT * FROM a 
GO
SELECT * FROM b
GO
SELECT * FROM c
GO

-- * Disabled by default. 
-- * To enable:
--    * ALTER DATABASE AdventureWorks SET RECURSIVE_TRIGGERS ON
--  * Considerations:
--    * Can exceed the 32-level nesting limit without careful design and thorough testing
--    * Can be difficult to control the order of table updates
--    * Can be replaced with nonrecursive logic