آموزش کار با تریگر

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

تریگر شیی در 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 چون نمیشه به اطلاعاتش مستقیم دست زد.

موفق باشید.

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

نقل قول:

---

ali190

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

---

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

نقل قول:

---

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

---

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

تریگر تقریبا همون رویداده که 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

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

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

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

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

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

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

تریگرهای 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 را حذف کنید.
دیدن این لینک هم خالی از لطف نیست.

سلام ممنون بابت توضیحات خوبتون
یه مثال میزنم میشه کمکم کنید
من دوتا جدول دارم 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

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

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

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

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

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

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 استفاده کن و هر دو عمل حذف رو در یک تریگر انجام بده.
اول باید تمام فرزندان اون مشتری پاک شن و بعد خود مشتری.

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

نقل قول:

---

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

---

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

نقل قول:

---

نوشته شده توسط You-See

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

---

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

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

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

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

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

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

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

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

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

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

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