IF EXISTS (SELECT * FROM sys.triggers WHERE NAME= 'T_Categories_I' AND type='TR' AND is_instead_of_trigger=	1)
	DROP TRIGGER T_Categories_I
GO

CREATE TRIGGER T_Categories_I
ON Production.Categories 
INSTEAD OF INSERT--, UPDATE--, DELETE   
AS 
	IF @@ROWCOUNT=0 RETURN
	SET NOCOUNT ON

	DECLARE @cInsertMode TINYINT
	SELECT @cInsertMode= CASE WHEN EXISTS (SELECT * FROM deleted) THEN 0 ELSE 1 END	

   select 'instead of - inserted', * from inserted
   select 'instead of - deleted', * from deleted

   if(@cInsertMode = 1) -- insert
   BEGIN
	   if not exists(select * from Production.Categories c, inserted where inserted.categoryname = c.categoryname)
			insert into Production.Categories
			(categoryname, description)
			select categoryname, description from inserted
		ELSE
			SELECT 'Kategoria juz istnieje'
	END
GO 

IF EXISTS (SELECT * FROM sys.triggers WHERE NAME= 'T_Categories_F' AND type='TR' AND is_instead_of_trigger= 0)
	DROP TRIGGER T_Categories_F
GO

CREATE TRIGGER T_Categories_F
ON Production.Categories 
FOR INSERT, UPDATE, DELETE   
AS 
	IF @@ROWCOUNT=0 RETURN
	SET NOCOUNT ON

   select 'for - inserted', * from inserted
   select 'for - deleted', * from deleted

GO

IF EXISTS (SELECT * FROM sys.triggers WHERE NAME= 'T_Categories_IU' AND type='TR' AND is_instead_of_trigger= 0)
	DROP TRIGGER T_Categories_IU
GO

CREATE TRIGGER T_Categories_IU
ON Production.Categories 
AFTER INSERT, UPDATE, DELETE   
AS 
	IF @@ROWCOUNT=0 RETURN
	SET NOCOUNT ON
	 
   --EXEC msdb.dbo.sp_send_dbmail  
   --     @profile_name = 'TSQL2012 Administrator',  
   --     @recipients = 'xx@xx',  
   --     @body = 'Dodano nową kategorię',  
   --     @subject = 'Informacja ważn';  

   select 'after - inserted', * from inserted
   select 'after - deleted', * from deleted

GO  



insert into Production.Categories values('kat5', 'opis5')

update Production.Categories SET categoryname = 'kat4new' where  categoryname = 'kat4'

delete from Production.Categories where  categoryname like 'kat4%'

select * from Production.Categories
go

--Właczanie i wyłączanie triggerów

DISABLE TRIGGER T_Categories_I ON Production.Categories;
GO
DISABLE TRIGGER T_Categories_I ON Production.Categories;
GO

insert into Production.Categories values('kat2', 'opis2')

go
ENABLE TRIGGER T_Categories_I ON Production.Categories;
GO


DISABLE TRIGGER T_Categories_I ON DATABASE;  
GO

DISABLE Trigger ALL ON ALL SERVER;  
GO  

ENABLE Trigger ALL ON DATABASE;  
GO