--use tempdb;
--GO
-- Create table T1
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1;
GO
CREATE TABLE T1
(
id INT NOT NULL PRIMARY KEY,
aa VARCHAR(10) NOT NULL
);
GO
-- AFTER či INSTEAd OF trigger je výhodné použiť pre danú úlohu?
IF OBJECT_ID('tr1', 'TR') IS NOT NULL DROP TRIGGER tr1;
GO
CREATE TRIGGER tr1 ON T1 AFTER UPDATE AS
IF UPDATE(id)
BEGIN
RAISERROR ('OK Csaba OK: nepovolene - tr1', 11, 1)
ROLLBACK TRAN
RETURN
END
GO
IF OBJECT_ID('tr2', 'TR') IS NOT NULL DROP TRIGGER tr2;
GO
CREATE TRIGGER tr2 ON T1 INSTEAD OF UPDATE AS
IF UPDATE(id)
BEGIN
RAISERROR ('OK Csaba OK: nepovolene - tr2', 11, 1)
ROLLBACK TRAN
RETURN
END
GO
INSERT INTO T1 SELECT 1, 'A';
UPDATE T1 SET id = 10 WHERE id = 1; -- nedovoli tr2
ALTER TABLE T1 DISABLE TRIGGER tr2
UPDATE T1 SET id = 10 WHERE id = 1; -- nedovoli tr1
ALTER TABLE T1 DISABLE TRIGGER tr1
UPDATE T1 SET id = 10 WHERE id = 1; -- dovolene
ALTER TABLE T1 ENABLE TRIGGER tr1
ALTER TABLE T1 ENABLE TRIGGER tr2