# پایگاه‌های داده > SQL Server > T-SQL >  آموزش کار با تریگر

## یوسف زالی

سلام.
چند تا پست دیدم که تو تریگر اشکال داشتند.
گرچه در این زمینه مطلب زیاد هست ؛ لازم دیدم کمی در موردش توضیح بدم. (با اجازه اساتید)

تریگر شیی در SQL هست که دستوراتی رو در مواقعی خاص (بعد از اقدام به تغییرات داده در جدول خاص - در عوض اعمال تغییرات داده در جدول خاص) برای ما اجرا می کنه که حتما لازم نیست روی جدول معین شده کار انجام بشه.
این شی دستورات خودش رو روی سرور اس کیو ال اجرا می کنه.
اما چرا تریگر؟
مواقعی هست که از چند طریق مختلف مثل پروسیجر ها - دستورات کامند ها در UI و یا جاهای دیگه ای اقدام به تغییر داده (Insert - Update - Delete) در جدول مشخصی می کنیم.
پس از مدتی متوجه میشیم که مثلا قبل از انجام این کارها باید کار مهمی رو انجام میدادیم. مثلا کنترل های تعدادی و ...
اگر بنا باشه که تمام جاهایی که از اون ها تغییرات داده روی جدول اعمال میشد رو دست بزنیم هم طولانی هست - هم گاهی باید EXE مجدد کامپایل شه - هم معمولا یکی دو جا از قلم می افته.
برای این کار یک تریگر تعریف می کنیم که مواظب جدول ما باشه و هر وقت هر چیزی خواست دست به این جدول بزنه (Select ایرادی نداره) به جاش یا بعدش (در تعریف مشخص می کنیم) دستوراتی رو اجرا کنه که این دستورات تقریبا می تونه هر چیزی باشه. (مثل دستوراتی که در Procedure می نویسیم . حتی Select کنه اما Select خالی که به درد نمی خوره)
در این جاست که کنترل خودمون رو تعریف می کنیم.

انواع تریگر :
تریگری که بعد از انجام کار باید اجرا شود (مثال : به روز رسانی جدول خاصی از روی مقادیر این جدول)
تریگری که به جای انجام کاری باید اجرا شود (مثال : کنترل موجودی - اگر موجودی کافی نبود نباید کار خواسته شده انجام شود)

نحوه تعریف تریگر:
Create Trigger Trig_Name On TableOrViewName After InsertOrUpdateOrDelete as bla bla
Create Trigger Trig_Name On TableOrViewName Instead Of InsertOrUpdateOrDelete as bla bla
Create Trigger Trig_Name On TableOrViewName For InsertOrUpdateOrDelete as bla bla

For در نسخه های قدیمی به جای After بود. هر دوش یه چیزه.

نکته مهم:
تریگر ها روی جداول فیزیکی می تونن گذاشته بشن نه جداول موقتی مثل # یا ## یا @.
تریگر ها پارامتر ندارند.

مثال های سریع:

Create Trigger Trig1 On Table1 Instead Of Insert, Update as blabla
Create Trigger Trig2 On Table2 After Delete, Insert as blabla
Alter Trigger Trig1 On Table3 For Delete as blabla

اما چه جوری با تریگر کار کنیم؟
در طول عمر یک تریگر دو تا جدول به وجود میان به نامهای Inserted و Deleted که از اونها می شه استفاده کرد. اما نمیشه بهشون دست زد.
به این جداول می گن جداول منطقی.
تو این جدولها اطلاعات جدولی هست که قرار بوده داده هاش رو دست بزنیم و ما رو اون تریگر گذاشتیم.
اگر گفته بودیم که حساس باشه به Insert کردن، اونوقت Inserted حاوی داده های ما بود که قرار بوده Insert شه یا Insert شده و Deleted خالیه. (بسته به نوع تریگر)
اگر گفته بودیم که حساس باشه به Delete کردن، اونوقت Deleted حاوی داده های ما بود که قرار بوده Delete شه یا Delete شده و Inserted خالیه.
اگر گفته بودیم که حساس باشه به Update کردن، اونوقت Deleted حاوی داده های قبلی این جدول بود و Inserted حاوی اطلاعات جدید ما که قرار بوده جایگزین بشه یا جایگزین شده.

حالا یه مثال یه کمی مشروح:
اگر جدولی داشته باشیم به نام Tbl1 با ستونهای ID و Amnt و تریگری مثل این تعریف کرده باشیم:
Create trigger Trig1 On TBL1 Instead Of Delete as 
delete TBL1 where ID in(select ID from Deleted)and Amnt > 1000

اونوقت هنگام Delete کردن اگر Amnt > 1000 باشه دستور ما اجرا میشه.
از کجا فهمیدیم چیا باید Delete شن؟

آفرین!


اما گاهی که تریگر همزمان برای چند کار مثل Update و Delete ست شده تو تریگر از کجا میشه فهمید چه کاری قرار بوده انجام بشه؟
از روی داده های جداول منطقی.
اگر Inserted پر بود و Deleted خالی بود = Insert
اگر Inserted خالی بود و Deleted پر بود = Delete
اگر Inserted پر بود و Deleted پر بود = Update


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

راستی! تریگر روی ویو هم مجازه اما فقط از نوع Instead Of چون نمیشه به اطلاعاتش مستقیم دست زد.

موفق باشید.

----------


## ali190

سلام
ممنون و متشکر از لطف شما
واقعاً عالی بود
خب ، چیزی که من از یکگ تریگر فهمیدم مثل یک event یا رویداد میمونه در یک IDE (مثل VB6 یا VB.NET) که در مقابل یک عمل از خودش عکس العمل نشون میده یا اصطلاحاً Fire میشه ، درسته؟
میشه فرق بین  تریگر After و Instead Of رو یه مقدار Detail تر بگید؟
آیا میشه در یک تریگر دو تا دستور Sql رو تعریف کرد؟
مثال:
فرض کنید من یه جدول دارم که یه فیلد ID داره
من میخوام حالت Autonumber رو بصورت دستی در این جدول اجرا کنم
به این صورت که یه جدول دیگه به نام t2 دارم که میخوام هر سری که در جدول t1 داده وارد میکنم شماره Id جاری به اضافه 1 بشه و در جدول t2 ثبت بشه
نکته مهم در خصوص t2 این هست که همیشه در این جدول یک رکورد وجود داره
میخوام در این تریگر ابتدا اولین رکورد جدول t2 حذف بشه بعد اون رکورد (id+1) درش اضافه بشه
ممنون و متشکر از لطف شما
یاعلی

----------


## Galawij

> ali190 
> نقل قول: آموزش کار با تریگر
> آیا میشه در یک تریگر دو تا دستور Sql رو تعریف کرد؟
> مثال:
> فرض کنید من یه جدول دارم که یه فیلد ID داره
> من میخوام حالت Autonumber رو بصورت دستی در این جدول اجرا کنم
> به این صورت که یه جدول دیگه به نام t2 دارم که میخوام هر سری که در جدول t1 داده وارد میکنم شماره Id جاری به اضافه 1 بشه و در جدول t2 ثبت بشه
> نکته مهم در خصوص t2 این هست که همیشه در این جدول یک رکورد وجود داره
> میخوام در این تریگر ابتدا اولین رکورد جدول t2 حذف بشه بعد اون رکورد (id+1) درش اضافه بشه



بله. شما هر تعداد که دستور بخواید، می تونید بنویسید.

----------


## ali190

> بله. شما هر تعداد که دستور بخواید، می تونید بنویسید.


میشه مثال بزنید؟

----------


## یوسف زالی

تریگر تقریبا همون رویداده که Object اون جدول یا ویو هست.
تفاوت After و Instead Of فقط در نوع اجراشه.
After پس از اعمال تغییرات ران میشه یعنی دستورات جدولی شما اجرا شده سپس تریگر Call میشه.
Instead Of به جای اعمال تغییرات ران میشه.
یعنی تغییرات شما اصلا اعمال نمی شه مگر اینکه در تریگر دوباره انجام بشه.
تریگر هم مثل پروسیجر می تونه چندین دستور داشته باشه.
بعد از As هر چند تا می خواهید دستور بنویسید.
معمولا این دستورات رو در Begin و End میگذارند اما الزامی هم به این کار نیست.

Create Trigger TG1 On TBL1 After Insert AS
begin
Command 1
Command2
.
.
Command N
end

----------


## ali190

مثلاً:
CREATE Trigger b On dbo.Table1 after insert
as
begin
delete from table2
Insert into table2
select * from inserted
end
ممنون از توضیحات کاملتون راجع به تریگر
با تسلط به زبان sql میشه از همه اون دستورات در تریگر استفاده نمود؟
یه خواهش دیگه هم داشتم
حالا که تقرباً با تریگر آشنا شدیم میشه چند تا مثال مفهومی از کاربرد تریگر در دتیابیس با اهداف مختلف بزنید (کد نیاز نیست فقط توضیح)
ممنون 
یاعلی

----------


## یوسف زالی

در تریگر فقط نمی شه دیتابیس رو دستکاری کرد (مثلا حذف اون) اما تقریبا هر دستوری میشه نوشت.
حتی میشه رو دیتابیس دیگه ای کار کرد.

مثالهایی از جایگاه استفاده از تریگر:
نگهداشتن سابقه یک رکورد برای مشتری. برای مشتری ها عملیاتی که اتفاق می افته روشون مثل تغییر آدرس و تلفن، میشه رکورد قبلیشون رو در جدول دیگه ای دخیره کرد.
به روز رسانی اعتبار مشتری. پس از ثبت فروش برای یک مشتری با پرداخت چکی در فاکتور او میشه جدول اعتباراتش رو به روز رسانی کرد.
کنترل موجودی. قبل از انجام ثبت حواله از انبار میشه موجودی اون کالا رو اول کنترل کرد.
کنترل اعتبار مشتری.
در مواقعی دستکاری داده ها در دیتابیس موازی (دیتابیس قانونی)
و ...

اما معمولا این کار ها رو میشه بدون تریگر هم انجام داد.

----------


## ali190

سلام
ممنون از توضیحاتتون
عکس العمل تریگر در دیتابیس چه عکس العملی در IDE دارد ؟
آیا کاربر در هنگام اجرا شدن تریگر چیزی رو حس میکنه؟

----------


## یوسف زالی

فکر نمی کنم Object در IDE وجود داشته باشه که از Trigger اطلاع داشته باشه.
اگر هم هست من ندیدم.

----------


## Galawij

تریگرهای DDL:
اینجا در مورد این نوع تریگرها توضیحی داده نشده بود، به خاطر همین خواستم آموزش را کاملتر کنم:
نگارشهاي قبليSQL SERVER تنهابه تريگرها امكان ميدادند تا با رويدادهاي دستكاري داده نظير درج يابهنگام رساني يك رديف استفاده شوند. SQL SERVER 2005 اين مسأله را با مجاز ساختن تريگرها براي قرارگرفتن در رويدادهاي DDLازقبيل ايجاد وحذف اشياي پايگاه داده ازقبيل جداول،ديدگاهها،رويه ها و login هاتوسعه داده است. تريگرهاي DDL ميتوانند با عبارات CREATE, ALTER,DROPمرتبط شوند. اين امر به DBAامكان قراردادن محدوديتهايي روي نوع عملياتي DDLاي راميدهدكه ميتوانند دريكپايگاه داده معين انجام شوند يا مي توانيد از اين تريگرها براي ارسال پيامهاي هشدار باتوجه به تغييرات مهم طرحوارهاي كه درپايگاه داده انجام شده است، استفاده کنید.
اين مثال نحوه افزودن يك تريگر DDL به نام NoTableUpdate رابه عبارات DDL، DROP TABLE، ALTER TABLEنشان مي دهد: 

CREATE TRIGGER NoTableUpdate
ON DATABASE FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'DROP TABLE and ALTER TABLE statement are not allowed'
ROLLBACK

دراينجا،مي توانيدببينيدكه چگونه تريگر DDLجديدمی تواندبراي محدودكردن كاربرد عبارات DROP TABLE، ALTER TABLE صادر شود، تريگرNoTableUpdate يك پيام خطا را چاپ خواهد كرد و عمل DDL مذکور ROLLBACK می شود. يك تلاش براي صادركردن يك عبارت ALTER TABLEدرپايگاه داده حاوي تريگر NoTableUpdate در اينجا نشان داده شده است:

DROP TABLE and ALTER TABLE statement are not allowed
.Net SqlClient Data Provider: Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.

براي اعمال تغييرات جداول در يك پايگاه داده بعد ازانجام اين تريگر،ابتدا بايد تريگر DDL را حذف کنید.
دیدن این لینک هم خالی از لطف نیست.

----------


## a.khosroabadi

سلام ممنون بابت توضیحات خوبتون
یه مثال میزنم میشه کمکم    کنید
من دوتا جدول دارم t1 , t2 تو t1 اطلاعات عمومی مشترکینم رو نگه میدارم و در t2 اطلاعات اختصاصیشون رو نگه میدارم
حالا میخوام وقتی یکی از سطرهای t1 رو پاک میکنم سطرهایه متناظر با اون در جدول t2 هم پاک بشه میشه بگید کدش چجوری میشه؟
میشه یه مثال برای کدش بنویسید ممنون میشم ازتون

----------


## یوسف زالی

ببینید مشخصات سطری که پاک می شه در جدول deleted در خود تریگر وجود داره.
روی فیلدهایی که می شه از طریق اونها تناظر رو برقرار کرد کار کنید.
من جداولتون رو ندارم. بنابراین مثال ساده ای می زنم تا کمک کنه:

Create trigger Trig1 On TBL1 Afte Delete as --sql 
delete TBL2 from Deleted where TBL2.ID = Deleted.ID-- sql

----------


## a.khosroabadi

ممنونم ازتو من دوتا جدولم به هم ارتباط دارن یک ریلیشن بینشون دارم وقطی میخوام پاک کنم پیغام میده دلیلش چیه؟بخار کلید خارجی هستش؟

----------


## یوسف زالی

دوست من ریلیشن؟ متن پیغام خطا؟ هیچ اطلاعاتی نمی دی چجوری کمک کنم؟
فرمت جداول و کد تریگر و متن پیغام خطا رو بگذار

----------


## a.khosroabadi

ببخشید چشم الان میزارم
اول جدول مشترکینم رو میزارم بعدش هم جدول اطلاعات جزئی و بعد هم تریگری که نوشتم


CREATE TABLE [dbo].[Costumers](
    [Costumer_ID] [int] IDENTITY(1,1) NOT NULL,
    [First_Name] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Last_Name] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Official_Name] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Job_Name] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Birth_Date] [smalldatetime] NULL CONSTRAINT [DF_Costumers_Birth_Date]  DEFAULT ((0)),
    [Location_BirthDate] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Costumers_Location_BirthDate]  DEFAULT ((0)),
    [Sex] [tinyint] NULL CONSTRAINT [DF_Costumers_Sex]  DEFAULT ((0)),
    [Marriad] [tinyint] NULL CONSTRAINT [DF_Costumers_Marriad]  DEFAULT ((0)),
    [Black_List] [tinyint] NULL CONSTRAINT [DF_Costumers_Black_List]  DEFAULT ((0)),
    [Costumer_Type] [tinyint] NULL CONSTRAINT [DF_Costumers_Costumer_Type]  DEFAULT ((0)),
    [Activity_ID] [int] NULL,
    [Department_ID] [int] NOT NULL,
    [Favourits] [tinyint] NULL CONSTRAINT [DF_Costumers_Favourits]  DEFAULT ((0)),
 CONSTRAINT [PK_Costumers_1] PRIMARY KEY CLUSTERED 
(
    [Costumer_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [phonNumber]
GO
ALTER TABLE [dbo].[Costumers]  WITH CHECK ADD  CONSTRAINT [FK_Costumers_Activity] FOREIGN KEY([Activity_ID])
REFERENCES [dbo].[Activity] ([Activity_ID])
GO
ALTER TABLE [dbo].[Costumers]  WITH CHECK ADD  CONSTRAINT [FK_Costumers_Department] FOREIGN KEY([Department_ID])
REFERENCES [dbo].[Department] ([Department_ID])




CREATE TABLE [dbo].[costumer_Phon_Details](
    [Row_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Costumer_ID] [int] NOT NULL,
    [Type_ID] [tinyint] NOT NULL,
    [Details] [nvarchar](200) COLLATE Arabic_CI_AS NOT NULL,
 CONSTRAINT [PK_costumer_Phon_Details] PRIMARY KEY CLUSTERED 
(
    [Row_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [phonNumber]
GO
ALTER TABLE [dbo].[costumer_Phon_Details]  WITH CHECK ADD  CONSTRAINT [FK_costumer_Phon_Details_costumers] FOREIGN KEY([Costumer_ID])
REFERENCES [dbo].[Costumers] ([Costumer_ID])
GO
ALTER TABLE [dbo].[costumer_Phon_Details]  WITH CHECK ADD  CONSTRAINT [FK_costumer_Phon_Details_PhonType] FOREIGN KEY([Type_ID])
REFERENCES [dbo].[phonTypes] ([Type_ID])



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        Alireza
-- Create date: <Create Date,,>
-- Description:    Delete Phon types
-- =============================================
ALTER TRIGGER [TR_Delete_Phon_Details]
   ON [dbo].[costumer_Phon_Details]
   AFTER  DELETE
AS 
BEGIN


    Delete dbo.costumer_Phon_Details where Costumer_ID in (Select Costumer_ID from Deleted)

END

----------


## a.khosroabadi

این پروسیجر رو برای حذف نوشتم وقتی اجراش میکنم پیغام ارور زیرش رو میده



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[Sp_Delete_Costumers]
(
    @CID as int
)

as
begin
    delete from dbo.Costumers
    from dbo.Costumers 
    where dbo.Costumers.Costumer_ID=@CID

end




Msg 547, Level 16, State 0, Procedure Sp_Delete_Costumers, Line 9
The DELETE statement conflicted with the REFERENCE constraint "FK_costumer_Phon_Details_costumers". The conflict occurred in database "phonNumber", table "dbo.costumer_Phon_Details", column 'Costumer_ID'.
The statement has been terminated.

----------


## یوسف زالی

شما به جای after از instead of استفاده کن و هر دو عمل حذف رو در یک تریگر انجام بده.
اول باید تمام فرزندان اون مشتری پاک شن و بعد خود مشتری.

----------


## a.khosroabadi

پس اینجوری که شما میگید من باید پروسیجرم رو تغییر بدم به شکلی که اول اطلاعات جدول خصوصیم رو پاک کنه بعد از طریق تریگر بره رکورد جدول مشتری هام رو پاک کنه؟

----------


## یوسف زالی

> من باید پروسیجرم رو تغییر بدم


روشی که ارائه شد روش حل تریگری اون مساله بود.
اگر قرار هست با SP این کارو کنید راههای ساده تری هم هست.
حتی می تونید با استفاده از رابطه هم این کارو کنید.
اما دوست من ادامه این مساله رو اینجا متوقف می کنم تا بحث منحرف نشه.
در پیغام خصوصی آدرس پستی که می رنید رو بهم بدید.
اونجا در این خصوص توضیح می دم.
موفق باشی.

----------


## mosafer_deltang

> سلام.
> اما گاهی که تریگر همزمان برای چند کار مثل Update و Delete ست شده تو تریگر از کجا میشه فهمید چه کاری قرار بوده انجام بشه؟
> از روی داده های جداول منطقی.
> اگر Inserted پر بود و Deleted خالی بود = Insert
> اگر Inserted خالی بود و Deleted پر بود = Delete
> اگر Inserted پر بود و Deleted پر بود = Update 
> موفق باشید.



چطوری باید بفهمیم inserted یا deleted خالیه یا پر
منظورم اینه که با چه کدی

----------


## یوسف زالی

خب با این کد:
if exists(select top 1 1 from Inserted) DoSomething

----------


## MRoustaei

سلام دوستان کسی هست مشکل من رو حل منه
من یه تریگر روی جدول فروشم نوشتم که هروقت کالایی فروخته بشه از موجودیم کم کنه.
من از دستور insert into به همراه select که همه محتوای فاکتور رو میریزه تو جدول فروشم.
تا اینجا مشکل ندارم.
مشکل اینجاس که فقط تغییرات روی موجودی بر اساس اولین کالای وارد شده توی فاکتور صورت میگیره 
انگار فقط اولین کالایی که تو فاکتور هست رو از موجودی کم میکنه بقیه کالاها از موجودی کم نمیشه 
اگه میشه بگید مشگلم کجاس
insert into forosh
(code,name)
select code,name
شبیه این هست دستورم

----------


## Hossis

میشه بعد از کلمه کلیدی After دو دستور Update و Insert رو با هم نوشت؟ به این مضمون که وقتی یک رکوردی *آپدیت* شده یا *اضافه* شد، مثلا در جدول لوگ ثبت بشه؟

----------


## یوسف زالی

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

----------


## شاهرخ عشایری

با سلام خدمت دوستان...
یه مشکلی با تریگر ها دارم و اون اینکه...
یه جدولی دارم و می خوام زمانیکه رکوردی بهش اضافه میشه ID اون رکورد رو برگردونه.... ID از نوع Auto-Increment هستش.

----------


## یوسف زالی

ببینید Identity@@ جواب می ده؟
اگر نه، جدول inserted رو بررسی کنید.

----------


## hosseinonline

تریگری که حذف رکورد از جدول را ممنوع کند و همون لحظه جلوی حذف رکورد رو بگیره و بیغام بده

----------


## یوسف زالی

چشم. امر دیگه؟؟

----------


## hosseinonline

:لبخند:  شرمنده خواستم سرعت به جواب رسیدن تو انجمن رو تست کنم!!!
لطفا راهنمایی کنید دوستان

----------


## یوسف زالی

شما باید تریگری بنویسید بصورت INSTEAD of Delete و در اون هیچ کاری انجام ندهید، یا این که شرطی رو توش تست کنید.

----------

