Rapasoft.eu

--Triggery - spustace, ktore spustaju niektore akcie pri (typ DML) insert, delete, update a pod.
/*
CREATE TRIGGER nazov ON tabulka
FOR -- AFTER alebo INSTEAD OF, v zavislosti od case spustenia 3gera
INSERT -- UPDATE alebo DELETE alebo ich kombinacie
AS
--co sa ma vykonat
*/

--priklad

USE tempdb
GO

IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
GO

CREATE TABLE T1 (
	x int
)
GO

IF OBJECT_ID('tiger','TR') IS NOT NULL DROP TRIGGER tiger
GO

CREATE TRIGGER tiger ON T1
AFTER 
INSERT, UPDATE
AS
	DECLARE @n int
	SET @n = @@rowcount
	DECLARE @c VARCHAR(50)
	SET @c = 'Tabulka T1 zmenena, pocet ovplyvnenych riadkov ='
	IF @n = 0 SET @c = @c + Cast(0 as CHAR(10))
	ELSE SET @c = @c + Cast(@n as CHAR(10))
	SET @c = @c + Cast(@@rowcount AS CHAR(10))
	PRINT @c
	SELECT * FROM deleted --vypise to co bolo predtym
	SELECT * FROM inserted --vypise tie ktore sa vlozili

SET NOCOUNT ON
GO

INSERT INTO T1 VALUES(1)
GO 
--vypise 1

INSERT INTO T1 SELECT 11 UNION ALL SELECT 111
GO
--vypise 2

UPDATE T1 SET x = 0
GO
--vypise 3

IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
GO