×

Trigger در SQL Server

فصل بیست و هفتم :

Trigger

یک تریگر نوع خاصی از sp است که هنگامی که اتفاق خاصی در اس کیوال سرور می افتد بطور اتوماتیک اجرا میشود .
تریگر ها بر سه نوع هستند dml , ddl و Logon Trigger

DML Triggers

هنگامی اجرا میشوند که کاربر بوسیله دستورات dml insert , delete , update ) (سعی در تغییر اطلاعات دارد.

DDL Triggers

در هنگام اجرای دستورات ddl اجرا میشوند (Create , alter,drop)

Logon Trigger

هنگامی که یک کاربر یک session جدید ایجاد میکند اجرا میشوند .

DML Triggers

CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS


FOR | AFTER
بعد از اتمام کامل و موفقیت امیز اعمال تعریف شده محتویات تریگر بایستی انجام شود .
(در view قابل استفاده نیست)
INSTEAD OF
محتویات تریگر بجای عمل مشخص شده انجام شوند
تریگری بروی جدول شاگردان بنویسید که اگر آپدیتی بروی این جدول رخ داد زمان آنرا در جدولی ذخیره کند.
create trigger t1 on student
for update
as
insert into mylog ([TimeOfAct]) values (GETDATE())

تریگر های dml از دو جدول موقت خاص استفاده میکنند که خود اس کیوال سرور انها را میسازدو مدیریت میکند که در حافظه هستند . اطلاعات این دو جدول را نمی توان بصورت دستی تغییر داد .
Deleted , Inserted
هنگامی که تریگری بروی event insert تنظیم میکنیم اس کیوال سرور یک کپی از رکورد های insert شده در این جدول ذخیره میکند.
هنگامی که عمل delete و یا update انجام میشود اس کیوال سرور یک کپی از رکورد های تغییر یافته را در جدول deleted نگهداری میکند.

حالا ستون های ذیل را به جدول mylog می افزاییم

Id int Unchecked
TimeOfAct datetime Checked
NewFirstName nvarchar(100) Checked
OldFirstName nvarchar(100) Checked
ChangedID smallint Checked

alter trigger t1 on student
for update
as

declare @NewFirstName nvarchar(100);
declare @OldFirstName nvarchar(100);
declare @ChangedID smallint;
select @NewFirstName=firstname,@ChangedID=id from inserted
select @OldFirstName=firstname from deleted
insert into mylog ([TimeOfAct],[NewFirstName],[OldFirstName],[ChangedID])
values (GETDATE(),@NewFirstName,@OldFirstName,@ChangedID )
تریگریی بنویسید که جلو آپدیت فیلد های جدول شاگردان را بگیرد
create trigger t2 on student
instead of update
as
insert into mylog([TimeOfAct]) values (getdate())

مثال : تریگری بروی جدول student بنویسید که با بروز هر تغییر در رکورد های جدول به فیلد FirstName کلمه 'آقای' را اضافه کند
create trigger t3 on student
instead of update
as
declare @firstname nvarchar(100);
declare @ID smallint;
select @firstname=firstname,@id=ID from inserted
update Student set FirstName=N' آقای ' + @firstname where id=@ID and FirstName not like N'%آقای%'

یکی از مهمترین کاربرد های instead of بررسی صحت اطلاعات یا همان data Validation است.
به فیلم مراجعه کنید

DDL Triggers

کاربرد آنها در :
جلوگیری از بروز تغییرات خاص در دیتابیس
با تغییر ساختار اتفاق خاصی بیفتد
این تریگر ها می توانند بروی یک دیتابیس و یا کل سرور تنظیم شوند

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK;
برای کل سرور
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
این تریگر ها برای event هایی نظیر
DROP_DATABASE
Alter_DATABASE
که در سطح سرور هستند server level
ویا
ایجاد و تغییر و حذف : جدول pk , view, , index , function, ,procedure ,
کاربرد دارند

LOGON Triggers

این تریگر ها در هنگام رخداد event login اجرا میشوند این event هنگامی رخ میدهد که یک session یک کاربر به instance اسکیوال سرور برقرار میگردد. اگر لاگین موفقیت امیز باشد . این تریگر بین احراز هویت و ایجاد session اجرا میشود. از این تریگر ها برای اطلاع و کنترل session های سرور می توان استفاده کرد.

CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

create TRIGGER Sample_logon_Trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
INSERT INTO School.dbo.mylog ([TimeOfAct],NewFirstName) values (getdate(),'login')

END

C:\Users\x>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER Sample_logon_Trigger ON ALL SERVER
2> GO


به فیلم مراجعه کنید
برای فعال کردن یک تریگر

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

برای غیر فعال کردن :

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

مثلا : DISABLE TRIGGER Person.uAddress ON Person.Address;
غیر فعال کردن تمامی تریگر ها
DISABLE Trigger ALL ON ALL SERVER;



مفاهیم و موضوعات این فصل عبارتند از :

  • SQL Server Triggers

  • DML Triggers

  • DDL Triggers

  • Logon Trigger

  • اتصال به سرویس SQL Server از طریق Command prompt

فصل بعدیفصل قبلی
هر گونه کپی برداری از مطالب این وبگاه تنها با ذکر نام مولف و لینک سایت مجاز است .
vatantop
نظرات و سوالات درج سوال/ نظر