T-SQL – Triggers

Triggers – are piece of code executed when insert/ update/ delete command is executed.

You can create trigger instead or after executed command.
You can run trigger when row is inserted, updated or deleted.
You can query edited data using inserted and deleted tables.

After trigger example.

CREATE TABLE Production.ProductUpdates -- create table to store information about edited data
(
	added INT,
	removed INT,
	pointInTime DATETIME2
);
GO

CREATE TRIGGER Production.tr_product_update -- create after trigger
ON Production.Products 
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
	IF @@ROWCOUNT <=0 RETURN; -- for performance reasons
	SET NOCOUNT ON; -- for performance reasons
	DECLARE @insertedCount AS INT = (SELECT COUNT(*) FROM inserted);
	DECLARE @removedCount AS INT = (SELECT COUNT(*) FROM deleted);
	INSERT INTO Production.ProductUpdates VALUES(@insertedCount, @removedCount, CURRENT_TIMESTAMP);
END;

INSERT INTO Production.Products VALUES('Product 1', 1, 1, 10, 0); -- insert product to run the trigger
SELECT * FROM Production.ProductUpdates; -- one row is added for inserted product (added = 1)

UPDATE Production.Products SET productname = 'Product 1a' WHERE productname = 'Product 1'; 
SELECT * FROM Production.ProductUpdates; -- one row is added for updated product (added = 1, removed = 1)

DELETE FROM Production.Products  WHERE productname = 'Product 1a';
SELECT * FROM Production.ProductUpdates; -- one row is added for deleted product (removed = 1)

Instead of trigger example.

DROP TRIGGER Production.tr_product_update; -- delete previous trigger
CREATE TRIGGER Production.tr_product_update -- create instead of trigger
ON Production.Products 
INSTEAD OF INSERT, UPDATE
AS
BEGIN
	IF @@ROWCOUNT <=0 RETURN; -- for performance reasons
	SET NOCOUNT ON; -- for performance reasons
	
	PRINT 'You cannot insert or update products now!';
END;

INSERT INTO Production.Products VALUES('Product 2', 1, 1, 10, 0); -- try to insert product to run the trigger
SELECT COUNT(*) FROM Production.Products WHERE productname = 'Product 2'; -- returns 0, row is not inserted

Trigger can rollback transaction which started it. See the example below.

DROP TRIGGER Production.tr_product_update; -- delete previous trigger
CREATE TRIGGER Production.tr_product_update
ON Production.Products 
INSTEAD OF INSERT, UPDATE
AS
BEGIN
	IF @@ROWCOUNT <=0 RETURN; -- for performance reasons
	SET NOCOUNT ON; -- for performance reasons
	
	PRINT 'You cannot insert or update products now!';
	ROLLBACK; -- rollback transaction which started the trigger
END;
GO

BEGIN TRAN -- start transaction which tries to insert product, then starts trigger and finally trigger rollbacks whole transaction
DECLARE @categoryId INT = (SELECT categoryid FROM Production.Categories WHERE categoryname = N'Drinks');
IF @categoryId IS NULL
BEGIN
	INSERT INTO Production.Categories VALUES(N'Drinks', N'Everything to drink');
	SET @categoryId = (SELECT IDENT_CURRENT('Production.Categories'));
END

INSERT INTO Production.Products VALUES('Water', 1, @categoryId, 4, 0);
COMMIT TRAN; -- transaction won't be commited as trigger rolls it back
GO

SELECT COUNT(*) FROM Production.Products WHERE productname = 'Water'; -- returns 0
SELECT COUNT(*) FROM Production.Categories WHERE categoryname = N'Drinks'; -- return 0
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s