május 2014 havi bejegyzések

Több soron futó triggerek

A triggerek az adatbázis szerverek egyik legveszélyesebb jószágai. A háttérben dolgoznak szépen csendesen, mindenki által elfeledve. Ráadásul a teljesítményre is rosszul hatnak (sőt, láttam már szervert is bedőlni miattuk). Persze azért találták ki, hogy éljünk velük, de amíg lehet, célszerű kerülni a használatukat, szerintem.

Az SQL szerverben a triggereket nincs lehetőség soronként (mint pl. Oracle-ben a “for each row”) futtatni, hanem utasításonként futnak le. Egyesek szerint tehetünk cursor-t a triggerbe (aztán szégyelljük el magunkat rendesen), de azzal nem a trigger fog soronként lefutni, hanem a feldolgozás.

A lényeg, hogy az inserted és deleted táblákban több sor is lehet. Ez ne feledjük! No és ezt miért mondom? Mert divat az inserted vagy deleted táblákból változókba tenni az értékeket és a későbbiekben azokkal dolgozni.

Végezetül demózzunk egy kicsit. Csinálunk egy users táblát a felhasználók adatinak tárolására és egy log táblát az users-hez, amibe mentjük minden módosításkor a módosult sorokat.

 

use tempdb
go

if object_id('tbl_users', 'U') is not null
	drop table [tbl_users]
go

if object_id('log_tbl_users', 'U') is not null
	drop table [log_tbl_users]
go

if object_id('trg_tbl_users_update', 'TR') is not null
	drop trigger trg_tbl_users_update
go

create table [tbl_users] (
	[id] int not null primary key,
	[name] varchar(255) not null,
	[password] varchar(36) not null,
	[user_hash] uniqueidentifier not null,
	[address] varchar(255) null
)
go

create table [log_tbl_users] (
	[id] int identity not null primary key,
	[tbl_users_id] int not null,
	[name] varchar(255) not null,
	[password] varchar(36) not null,
	[user_hash] uniqueidentifier not null,
	[address] varchar(255) null,
	[created_at] datetime default getutcdate()
)
go

;with cte as (
	select top 100
		row_number() over(order by (select null)) as id
	from sys.columns c
)

insert into [dbo].[tbl_users] (
		[id] ,
		[name] ,
		[password] ,
		[user_hash] ,
		[address]
	)
select
	id,
	concat('Name ', id) as [name],
	newid() as [password],
	newid() as user_hash,
	concat('Address ', id) as [address]
from [cte]

go
-- A trigger, ami csak 1 sor módosítására van felkészítve
create trigger trg_tbl_users_update on [dbo].[tbl_users]
after update
as
	declare
		@id int,
		@name varchar(255),
		@password varchar(36),
		@user_hash uniqueidentifier,
		@address varchar(255)

	select
		@id = [id],
		@name = [name],
		@password = [password],
		@user_hash = [user_hash],
		@address = [address]
	from deleted

	insert into [dbo].[log_tbl_users]
	        ( [tbl_users_id] ,
	          [name] ,
	          [password] ,
	          [user_hash] ,
	          [address]
	        )
	values (
		@id,
		@name,
		@password,
		@user_hash,
		@address
	)
go

Van egy 100 soros users táblánk és egy 0 soros log táblánk. Adjunk ki egy módosító parancsot, ami több sort érint:

begin tran
	update [dbo].[tbl_users]
	set [name] += '1'
	where [id] < 10

	select * from [dbo].[log_tbl_users] order by id
rollback
go

Az eredmény 1 sor.

049_tobbsoros_triggerek_result_1

Módosítsuk a triggerünket úgy, hogy alkalmas legyen több sor módosítását lekövetni:

alter trigger trg_tbl_users_update on [dbo].[tbl_users]
after update
as
	insert into [dbo].[log_tbl_users] (
			[tbl_users_id] ,
			[name] ,
			[password] ,
			[user_hash] ,
			[address]
	    )
	select
		[id],
		[name],
		[password],
		[user_hash],
		[address]
	from deleted
go

Futtassuk ismét az update-et:

begin tran
    update [dbo].[tbl_users]
    set [name] += '1'
    where [id] &lt; 10

    select * from [dbo].[log_tbl_users] order by id
rollback
go

Az eredményünk végre az elvárt:
049_tobbsoros_triggerek_result_2

 

És még egy fontos dolog. Az output  cause és a trigger nem kompatibilisek. Vagy egyik, vagy másik.