Instead of insert, update, delete

Not for replication

As

Begin

/*

Установка noucount on приводить до того, що не виводиться кількість оброблених командами T-SQL рядків

*/

Set nocount on

Declare

@var int

set @var = (select [object_id] from sys.objects where name = 'trig3'

and TYPE = 'TR'

and schema_id = (select schema_id from sys.schemas

where name = 'dbo')

)

if (TRIGGER_NESTLEVEL(@var, 'IOT', 'DML') = 1)

Begin

Declare

@count1 int,

@count2 int,

@count3 int,

@col_upd varbinary(128)

set @count1 = (select count(*) from table1)

set @count2 = (select count(*) from inserted)

set @count3 = (select count(*) from deleted)

set @col_upd = columns_updated()

if (@col_upd != 0)

Begin

if (@count2 = @count3)

Begin

- Випадок, коли тригер активований командою UPDATE

Declare

@col_name varchar(128),

@table_id int,

@col_id int,

@is_iden bit,

@is_comp bit,

@type_id tinyint,

@type_name sysname

- одержуємо ідентифікатор таблиці table1 із системеного подання sys.objects

set @table_id = (select [object_id] from sys.objects where

name = 'table1' and type = 'U' and

schema_id = (select [schema_id]

from sys.schemas where name = 'dbo')

)

set @col_id = 0

/*

При використанні команди EXECUTE не можна звертатися до таблиці inserted, тому копіюємо її вміст у тимчасову таблицю

*/

select * into #temp_ins from inserted

- відключаємо спрацьовування тригера trig3

Alter table table1 disable trigger trig3

- запускаємо цикл по тимі стовпцям, значення яких змінювалися

while (@col_upd <> 0)

Begin

- одержуємо поточний номер стовпця один по одному від початку таблиці

set @col_id = @col_id + 1

if ((@col_upd & 1) = 1)

Begin

- якщо цей стовпець піддавався зміні

/*

Одержуємо назву цього стовпця, інформацію про те, чи має він властивість identity або чи є він таким, що обчислюється або чи має він властивість identity, і ідентифікатор системного типу даних для цього стовпця

*/

select @col_name = name, @is_iden = is_identity,

@is_comp = is_computed,

@type_id = system_type_id

From sys.columns where

([object_id] = @table_id)

and (column_id = @col_id)

- Якщо стовпець не має властивості idenity і не є що обчислюється

if ((@is_iden <> 1) and (@is_comp <>1))

Begin

- Одержуємо назву типу даних для цього стовпця

set @type_name = (select name from sys.types

where system_type_id = @type_id)

/*

Використовуючи команду EXECUTE і конструювання пакета SQL команд у строковому виді за допомогою операції конкатенації рядків виконуємо наступні дії:

1. Оголошуємо курсор по таблиці #temp_ins, що зберігає змінені значення оброблюваного стовпця і значення первинного ключа.

2. Оголошуємо змінні, які будуть використовуватися при одержанні значень поточного рядка курсору.

3. Запускаємо цикл, у якому перебираємо по черзі всі рядки курсору.

4. У цьому циклі одержуємо значення зміненого стовпця для поточного рядка, а також значення первинного ключа і конструюємо відповідну команду update, що для оброблюваного стовпця змінює його значення на отримане при збігу первинних ключів.

5. По закінченні циклу закриваємо курсор.

Для таблиць, де можлива зміна первинного ключа потрібен більш складний алгоритм, тому що змінюваний стовпець може виявитися елементом первинного ключа.

*/

execute( 'declare cur_ins cursor local for

select ' + @col_name +

' from #temp_ins ' +

'open cur_ins ' +

'declare @value_new ' + @type_name + ', ' +

' @value_key int '+

'while (1 = 1) ' +

'begin ' +

' fetch next from cur_ins into @value_new ,

@value_key' +

' if (@@FETCH_STATUS = -1) ' +

break; ' +

' update table1 set ' + @col_name + ' =

@value_new where c3 = @value_key ' +

'end ' +

'close cur_ins '

);

End

End

- Переходимо до обробки наступного стовпця

set @col_upd = @col_upd / 2

End

-- Знову дозволяємо спрацьовування тригера

Begin

Enable trigger trig3 ON table1

End

End

Else

if (@count3 = 0)

Begin

- Тригер активований командою INSERT

Insert into table1(c1, c2) (select c1, c2 from inserted)

End

End

Else

- Тригер активований командою DELETE

delete from table1 from deleted d where table1.c3 = d.c3

End

End

Go

Після виконання наступної команди SQL:

update table1 set c1 = c1 + 1, c2 = c2 - 1

таблиця table1 буде містити наступні дані:

Якщо потім виконати команду SQL:

insert into table1(c1, c2) (select c1, c2 from table1 where c2 = 4)

то таблиця table1 буде містити наступні дані:

У випадку виконання команди SQL:

delete from table1 where c1 = 3

над отриманою після попереднього кроку таблицею table1, будемо мати наступний результат:

Отже, очевидно, що написаний тригер trig3 спрацьовуючи замість відповідних SQL команд повністю їх повторює.

DDL тригери.

Приклад 4.

Створення найпростішого DDL тригера, що реагує на спробу видалення таблиці table1 і викликає у цьому випадку відкат транзакції.

/*

Перевіряємо наявність DDL тригера (поле parent_type = 0) з обраним іменем і якщо такий тригер існує, то знищуємо його, враховуючи те, що він має рівень БД. У випадку створення DDL тригера рівня сервера, перевірку його існування і, в разі потреби, знищення, потрібно було б здійснювати таким чином:

if exists(select name from sys.server_triggers where name = 'trig_ddl1')