Trigger ها چرا چطور چگونه

[hamed_Arfaee]

سلام و دو صد بدرود

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

تريگر نوع خاصي از روال ذخيره شده است و در هنگام تغيير داده اي كه براي حفاظت از آن طراحي شده است فعال مي گردد. تريگرها با جلوگيري از تغييرات غير معتبر يا ناسازگار در داده ها به حفظ يكپارچگي داده اي كمك مي كنند. فرض كنيد دو جدول customer و orders در اختيار داريد بنابراين مي توانيد تريگري ايجاد كنيد كه با ايجاد هر سفارش جديد، اعتبار شماره مشتري آن را كنترل كند. همچنين مي توانيد تريگري ديگري ايجاد كنيد كه در صورت حذف يك مشتري جدول orders را كنترل كرده و در صورت وجود سفارش براي آن مشتري فرآيند حذف مشتري را متوقف سازد.
تريگرها مي توانند قواعد كاري پيچيده تري را نسبت به قيود اعمال كنند. براي مثال يك تريگر insert مي تواند در هنگام درج ركورد جديد به سفارشات فعال شود و وضعيت پرداخت مشتري را بررسي و نوع پرداخت مناسب وي را تعيين كند.
تريگرها پارامتر ندارند و صريحا فعال نمي شوند و براي فعال سازي آنها تغييرات داده اي لازم است.
تريگرها را مي توان تا 32 سطح تودرتو تعريف كرد. اين تريگرها به شكل زير عمل مي كنند:
براي مثال تريگر جدول orders يك ورودي به جدول حسابهاي دريافتي اضافه مي كند و اين ورودي به نوبه خود تريگر ديگري را براي بررسي وضعيت مشتري فعال مي نمايد. يك تريگر به روز رساني جدول را انجام مي ده و همين امر تريگر ديگري را فعال مي كند.
بطور پيش فرض تمامي تريگرها (UPDATE,DELETE,INSERT)پس از تغييرات داده اي فعال مي شوند و به تريگر AFTER معروف مي باشند. در ويرايش هاي قبلي SQL Server تنها اين نوع تريگر موجود بوده است. در SQL Server 2000 نوع ديگري به نام INSTEAD OF ‌ معرفي شده است كه بجاي تغييرات داده اي موردنظر فعال مي شود.
از نقطه نظر كارائي، تريگرها سربار زيادي ندارند. بيشتر زمان اجراي يك تريگر براي ارجاع به ساير جدول بكار مي رود. اين ارجاع در صورت وجود جداول در حافظه سريع و در صورت نيار به خواندن از ديسك كمي كندتر خواهد بود.
تريگرها بخشي از يك تراكنش (transaction) محسوب مي شوند. اگر تريگر يا هر بخشي از تراكنش با شكست مواجه شود كل تراكنش عقبگرد مي شود.

منبع : computer-tech.blogfa.com
خلاصه مطلب بالا میشه اینکه  وقتی روی جدولی 3 عمل اصلی Insert – Update – Delete  اتفاق افتاد یه روالی شروع بکار بکند و این روال توانایی رد هر عملی انجام گرفته را دارد .
تریگر در سه سطح قابل پیاده سازی هست
1- سطح اینستنس
2- سطح دیتابیس
3- سطح جدول یا دیدگاه
که از پایین به بالا میریم
ساختار تولید تریگر بصورت زیر است

کد:

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::=
   [ ENCRYPTION ]
   [ EXECUTE AS Clause ]
<method_specifier> ::=
   assembly_name.class_name.method_name

توضیحاتش :

کد:

CREATE TRIGGER trigger_name

کلمه کلیدی ساخت تریگر و نام اون

کد:

ON  table | view

روی کدوم جدول و یا دیدگاه ساخته بشه

کد:

[ WITH ENCRYPTION ]

اختیاری : کد بشه - قابل ویرایش و کپی نباشه

کد:

   FOR | AFTER | INSTEAD OF

قبل از- بعد از- بجای

کد:

INSERT ,UPDATE,DELETE

اینم که مشخصه

کد:

       [ NOT FOR REPLICATION ]

اختیاری: این تریگر برای ستونی که مقدار not for replication اون فعال هست اجرا نشه
برای اطلاعات بیشتر در مورد not for replication
     
چند تا مثال :
در جداولی به ساختار زیر اطلاعات مشتری ، اطلاعات کالا هاو اطلاعات سفارشات وجود دارد :

کد:

CREATE TABLE [dbo].[customers](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [NameFamily] [nvarchar](50) NULL,
    [Credit] [money] NULL,
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Goods](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [GoodName] [nvarchar](50) NULL,
    [GoodCount] [int] NULL,
    [GoodFee] [money] NULL,
CONSTRAINT [PK_Goods] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Orders](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CID] [int] NULL,
    [GID] [int] NULL,
    [GCount] [int] NULL,
    [GSUM] [money] NULL CONSTRAINT [DF_Orders_GSUM]  DEFAULT ((0))
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_customers] FOREIGN KEY([CID])
REFERENCES [dbo].[customers] ([Id])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_customers]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Goods] FOREIGN KEY([GID])
REFERENCES [dbo].[Goods] ([Id])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Goods]

حالا تریگری که قبل از ثبت سفارش موجودی انبار و اعتبار مشتری رو چک میکنه و اگر کم بود اجازه ثبت نمیده :

کد:

CREATE TRIGGER [dbo].[checkBalance]
  ON  [dbo].[Orders]
 FOR insert
AS
BEGIN
    IF (SELECT goodcount FROM goods WHERE id=(SELECT gid FROM Inserted)) < (SELECT gcount FROM Inserted)
    BEGIN
        RAISERROR ('موجودي کالا کم است',10,1)
        rollback
    END
         IF (SELECT credit FROM customers WHERE id=(SELECT cid FROM Inserted)) <
          ((SELECT gcount FROM Inserted)*(SELECT goodFee FROM goods WHERE id=(SELECT gid FROM Inserted)))
    BEGIN
        RAISERROR ('موجودي حساب مشتری کم است',10,1)
        rollback
    END
    SET NOCOUNT ON;

END

تریگری که بعد از ثبت سفارش از موجودی انبار و اعتبار مشتری به میزان خرید کم میکنه و  بعد از حذف سفارش مقادیر بازگشتی رو در جداول کالا و مشتری بر میگردونه

کد:

CREATE TRIGGER [dbo].[setSum]
  ON  [dbo].[Orders]
 after INSERT,delete
AS
BEGIN
    SET NOCOUNT ON;
    IF exists(SELECT * FROM inserted)
    BEGIN
                  UPDATE orders SET gsum =((SELECT gcount FROM Inserted)*(SELECT goodFee FROM goods WHERE id=(SELECT gid FROM Inserted)))
                WHERE id=@@IDENTITY
                UPDATE customers SET credit=credit-(select gsum  FROM orders WHERE id=@@IDENTITY)WHERE id=(SELECT cid FROM inserted)
                UPDATE Goods SET Goodcount =goodcount-(select gcount  FROM orders WHERE id=@@IDENTITY)WHERE id=(SELECT gid FROM inserted)
    END
    ELSE
            BEGIN
                UPDATE customers SET credit=creadit+(select gsum  FROM deleted)WHERE id=(SELECT cid FROM deleted)
                UPDATE Goods SET Goodcount =goodcount+(select gcount  FROM deleted )WHERE id=(SELECT gid FROM deleted)
            END
END

و یا تریگری که بجای حذف اطلاعات مشتری موجودی اونو منفی میکنه :

کد:

CREATE TRIGGER [dbo].[insteadDelete]
  ON  [dbo].[customers]
 instead OF delete
AS
BEGIN
    IF (SELECT credit FROM deleted) >0
UPDATE customers SET credit=credit *-1 WHERE id=(SELECT id FROM deleted)
    SET NOCOUNT ON;
END

توضیح : با استفاده از RAISERROR میتونیم خطائی رو به کاربر و یا سطح بالاتر برگردونیم باعث تولید EVENT  میشه که بعدا توضیح میدم و ساختار کلیش برابر است با :

کد:

RAISERROR ( { msg_id | msg_str | @local_variable }
   { ,severity ,state }
   [ ,argument [ ,...n ] ] )
   [ WITH option [ ,...n ] ]

حالا یه جدول داریم با ساختار زیر :

کد:

CREATE TABLE [dbo].[logs](
    [db_table] [nvarchar](50) NULL,
    [db_command] [nvarchar](50) NULL,
    [db_commandtext] [nvarchar](MAX) NULL,
    [db_username] [nvarchar](50) NULL CONSTRAINT [DF_logs_db_username]  DEFAULT (suser_sname()),
    [db_domain] [nvarchar](50) NULL CONSTRAINT [DF_logs_db_domain]  DEFAULT (user_name()),
    [db_datetime] [nvarchar](50) NULL CONSTRAINT [DF_logs_db_datetime]  DEFAULT (getdate()),
    [db_app] [nvarchar](50) NULL CONSTRAINT [DF_logs_db_app]  DEFAULT (app_name())
) ON [PRIMARY]

میخواهییم وقتی اطلاعات هر کدوم از جدول های مشتری-کالا و سفارشات تغییر کرد(حذف-اضافه-بروز رسانی) در این جدول گزارشش ثبت بشه :
پس به هر کدوم از جدول ها این تریگر رو اضافه میکنیم :

کد:

CREATE TRIGGER [dbo].[LogIt]
  ON  [dbo].[customers]
  AFTER  INSERT,DELETE,UPDATE
AS
BEGIN
    
DECLARE @NewData nvarchar(MAX);
DECLARE @Event varchar (20);
 
   IF (EXISTS(SELECT * FROM INSERTED) AND  EXISTS(SELECT * FROM DELETED))
   BEGIN
       SET @NewData='id='+ cast((SELECT TOP 1 id FROM DELETED)AS varchar)+',NameFamily='+ (SELECT TOP 1 NameFamily FROM DELETED)+',Credit='+ cast((SELECT TOP 1 Credit FROM DELETED)AS varchar);
       SET @Event='UPDATE'        
   END
   else IF EXISTS(SELECT * FROM INSERTED)
   BEGIN
       SET @NewData='id='+ cast((SELECT TOP 1 id FROM inserted)AS varchar)+',NameFamily='+ (SELECT TOP 1 NameFamily FROM inserted)+',Credit='+ cast((SELECT TOP 1 Credit FROM inserted)AS varchar);
       SET @Event='INSERT'        
   END
   else IF EXISTS(SELECT * FROM DELETED)
   BEGIN
       SET @NewData='id='+ cast((SELECT TOP 1 id FROM DELETED)AS varchar)+',NameFamily='+ (SELECT TOP 1 NameFamily FROM DELETED)+',Credit='+ cast((SELECT TOP 1 Credit FROM DELETED)AS varchar);
       SET @Event='DELETE'        
   END
SET NOCOUNT ON;
INSERT INTO logs (db_table,db_command,db_commandtext)VALUES ('customers',@Event,@NewData)
END

نکته : در جدول مشتری ها اگر ردیفی را پاک کنیم گزارش آن بصورت آپدیت ثبت خواهد شد چرا که به جای حذف دستور آپدیت رو فراخوانی کردیم و همچنین اگر شخصی که حسابش منفی هست یعنی قبلاپاک شده رو مجدد پاک کنیم هیچ گزارشی ثبت نخواهد شد چرا که هیچ حذف و یا بروز رسانی اتفاق نمی افتاد 
توابعی وجود داره که براساس ارتباط فعلی مقدار دهی شدن
بعضی از کارآمدترین ها اینها هستن :

کد:

Suser_name نام  کاربری ویندوز کاربر
User_name نام کاربری که با ان به سرور وصل شدیم
Getdate تاریخ سرور
App_name نام برنامه ای که در ارتباط با سرور است
@@Rowcount تعداد رکورد هایی که در آخرین عمل سلکت انتخاب شدند
Db_name() نام دیتابیسی که در حال استفاده است
@@IDENTITY آخرین مقدار فیلد خود افزایشی

نکته : اگر توجه کنین کنین تو جدول گزارشات فقط 3 ستون رو مقدار دهی میکنیم و بقیه 4 تا ستون با مقادیر پیش فرض تکمیل می شن که این مقادیر با کمک توابع سیستمی اس کیو ال مقدار دهی شدن .

این مطلب ادامه دارد

[hamed_Arfaee]

قسمت دوم
حالا یه سوال اساسی :چطور میتونیم با این تریگر ها که باعث ثبت نشدن اطلاعات و یا با تغییر ثبت شدن اونها بودن در ارتباط باشیم ؟
نکته :استفاده از دستور Raiserror باعث تولید یک پیام خطا با مشخص بودن سطح اولویت میشه و استفاده از دستور Rollback باعث تغییر نیافتن اطلاعات و یک پیام خطا میشه به این متن :
The transaction ended in the trigger. The batch has been aborted.
اگر بخواهیم فقط متن خطای ما نشون داده بشه میشه یه کار ساده انجام داد :

کد:

BEGIN try
INSERT INTO Orders(CID, GID, GCount )VALUES(3,1,20)
END try
BEGIN catch
END catch

چون اون خطا یه ایکسپشن داخلی هست.
حالا ما نمیخواییم از دستور Rollback استفاده کنیم ولی نیاز داریم پیامی رو به سطح بالاتر بر گردونیم .
مثال : بعد از ثبت یک سفارش مانده حساب مشتری و مانده کالا در انبار رو میخواهیم برگردونیم ، طبیعتا اگر از Rollback استفاده نکنیم در سطح بالاتر هیچ ایکسپشنی دریافت نمیکنیم پس راه چاره چیست ؟
اطلاعات : منظورم از سطح بالا برنامه نهایی هست که کاربر داره از اون استفاده میکنه .

اگر دو کلاس زیر رو لایه دیتابیس خودمون فرض کنیم :
بعدا برای کاربرد کلاس دوم مثال میزنم .

کد:

Imports System.Data.SqlClient
Public Class Mydb
Private CNNString As String = ""
Private CNN As SqlConnection
Private AutoOC As Boolean = False
Public Event sqlInfoMessage As sqlInfoMessageEventHandler
Private Sub _New(ByVal dbConnectionString As String, ByVal AutoOpenClose As Boolean)
CNN = New SqlConnection(dbConnectionString)
CNNString = dbConnectionString

AutoOC = AutoOpenClose

AddHandler CNN.InfoMessage, New SqlInfoMessageEventHandler(AddressOf SqlEvent)
AddHandler CNN.StateChange, New StateChangeEventHandler(AddressOf CnnChangeState)
End Sub
Public Sub New(ByVal dbConnectionString As String, ByVal AutoOpenClose As Boolean)
_New(dbConnectionString, AutoOpenClose)
End Sub
Public Sub New(ByVal dbConnectionString As String)
_New(dbConnectionString, False)
End Sub
Public Sub Open()
If CNN.State <> ConnectionState.Open Then CNN.Open()
End Sub
Public Sub Close()
If CNN.State = ConnectionState.Open Then CNN.Close()
End Sub
Public ReadOnly Property GetConnection As SqlConnection
Get
Return CNN
End Get
End Property
Public ReadOnly Property GetConnectionString As String
Get
Return CNNString
End Get
End Property

Public Function RunSQL(ByVal sql As String) As Integer
Dim Result As Integer = 0
If AutoOC Then Open()
Dim CMD As New SqlCommand
If Not CNN.State = ConnectionState.Open Then
Throw (New Exception("Connection is closed"))
End If
Try
CMD.CommandText = sql
CMD.Connection = CNN
Return (CMD.ExecuteNonQuery())
Catch ex As SqlException
Throw (ex)
End Try
If AutoOC Then Close()
Return Result
End Function

Public Function SelectTable(ByVal sql As String) As DataTable
Dim Result As New DataTable
If AutoOC Then Open()
If Not CNN.State = ConnectionState.Open Then
Throw (New Exception("Connection is closed"))
End If
Try
Dim myAdaptor As SqlDataAdapter
myAdaptor = New SqlDataAdapter(New SqlCommand(sql, CNN))
myAdaptor.SelectCommand.CommandType = CommandType.StoredProcedure
myAdaptor.Fill(Result)
Result.TableName = "Arfaeih"
Catch ex As SqlException
Throw (ex)
End Try
If AutoOC Then Close()
Return Result
End Function

Public Function SelectSP(ByVal spName As String, ByVal ParamArray myData() As MyFields) As DataSet
Dim Result As New DataSet
If AutoOC Then Open()
If Not CNN.State = ConnectionState.Open Then
Throw (New Exception("Connection is closed"))
End If
Try
Dim myAdaptor As SqlDataAdapter
myAdaptor = New SqlDataAdapter(New SqlCommand(spName, CNN))
myAdaptor.SelectCommand.CommandType = CommandType.StoredProcedure
If Not IsNothing(myData) Then
Dim tmpFields As New MyFields
For I As Integer = 0 To UBound(myData)
tmpFields.Combine(myData(I).Getlist)
Next I
For Each mst As MyFields.Mystruct In tmpFields.Getlist
myAdaptor.SelectCommand.Parameters.AddWithValue(mst.MyField, mst.MyValue)
Next
End If
myAdaptor.Fill(Result)
Catch ex As SqlException
Throw (ex)
End Try
If AutoOC Then Close()
Return Result
End Function
Public Function SelectSP(ByVal spName As String) As DataSet
Return SelectSP(spName, Nothing)
End Function

Private Sub CnnChangeState(ByVal sender As Object, ByVal e As StateChangeEventArgs)

End Sub
Private Sub SqlEvent(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
RaiseEvent sqlInfoMessage(sender, e)
End Sub
Public Sub dispose()
CNN.Dispose()
GC.Collect()
End Sub
End Class
Public Class MyFields
Public Structure Mystruct
Dim MyField As String
Dim MyValue As Object
End Structure
Dim Allfields As New ArrayList
Public Sub Clear()
Allfields.Clear()
End Sub
Public Sub New()

End Sub
Public Sub New(ByVal myFname As String, ByVal myVal As Object)
Add(myFname, myVal)
End Sub
Public Sub Add(ByVal myFname As String, ByVal myVal As Object)
Dim NewStruct As New Mystruct
NewStruct.MyField = myFname
NewStruct.MyValue = myVal
Allfields.Add(NewStruct)
NewStruct = Nothing
End Sub
Public Sub Combine(ByVal newArray As ArrayList)
For Each mst As MyFields.Mystruct In newArray
Allfields.Add(mst)
Next
End Sub
Public Function Getlist() As ArrayList
Return Allfields
End Function
End Class

و ازش به این صورت استفاده کنیم :

کد:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim db As New Mydb(s, True)
Try
db.RunSQL("INSERT INTO Orders(CID, GID, GCount )VALUES(3,1,20)")
Catch ex As SqlException
MsgBox(ex.Errors(1).Number)
End Try
End Sub

هر پیامی که در سطح دیتابیس اتفاق بیافته و همراه با ایکسپشن بالا بیاد توسط توابع ما هندل میشن .
این کلاس کوچیک قدرت خوبی برای کار با دیتابیس داره و حق کپی رایتشم محفوظه .

یه سوال جدید : میخواهیم اون پیامی که بر میگردونیم همراه با شماره باشه نه فقط متن
برای اینکه Raiserror ما حاوی اطلاعات کاربردی زیادی باشه میتونیم از یک stored procedure سیستمی استفاده کنیم به این صورت :
میخواهیم بعد از ثبت و یا حذف سفارش مانده حساب مشتری به ما اعلام شود پس :

کد:

ALTER TRIGGER [dbo].[setSum]
ON [dbo].[Orders]
after INSERT,delete
AS
BEGIN
DECLARE @LeftCredit varchar(20);
SET NOCOUNT ON;
IF exists(SELECT * FROM inserted)
BEGIN
UPDATE orders SET gsum =((SELECT gcount FROM Inserted)*(SELECT goodFee FROM goods WHERE id=(SELECT gid FROM Inserted)))
WHERE id=@@IDENTITY
UPDATE customers SET credit=credit-(select gsum FROM orders WHERE id=@@IDENTITY)WHERE id=(SELECT cid FROM inserted)
UPDATE Goods SET Goodcount =goodcount-(select gcount FROM orders WHERE id=@@IDENTITY)WHERE id=(SELECT gid FROM inserted)
SET @LeftCredit=cast ((SELECT credit FROM customers WHERE id=(SELECT cid FROM inserted))AS varchar)
END
ELSE
BEGIN
UPDATE customers SET credit=credit+(select gsum FROM deleted)WHERE id=(SELECT cid FROM deleted)
UPDATE Goods SET Goodcount =goodcount+(select gcount FROM deleted )WHERE id=(SELECT gid FROM deleted)
SET @LeftCredit=cast ((SELECT credit FROM customers WHERE id=(SELECT cid FROM deleted))AS varchar)
END
EXEC sp_addmessage @msgnum = 500058, @severity = 10, @msgtext = @LeftCredit;
RAISERROR (500058, 10,1);
EXEC sp_dropmessage @msgnum = 500058;
END

نکته : فقطی یک پیامی شخص رو میخواهییم شماره گذاری کنیم و یعنی در کل برای استفاده از این استورد پروسیجر باید شماره خطاهای ما بزرگتر از 50000 باشن .
و در سطح بالاتر یعنی برنامه خودمان این کد ها رو داریم :

کد:

Private Sub SqlEvent1(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
MsgBox(e.Message, MsgBoxStyle.Critical, e.Errors(0).Number)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim db As New Mydb(s, True)
AddHandler db.sqlInfoMessage, AddressOf SqlEvent1
Try
db.RunSQL("INSERT INTO Orders(CID, GID, GCount )VALUES(2,1,2)")
Catch ex As SqlException
MsgBox(ex.Errors(1).Number)
End Try
End Sub

نکته
متغییر s همون Connection String هست که بصورت سراسری تعریف کردم:

کد:

Dim s As String = "app=Arfaee.h;Server=.;Database=mysale;Trusted_Connection=True;"

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

[hamed_Arfaee]

تریگر ها در یک سطح بالاتر یعنی سطح دیتابیس

تریگر ها در این سطح میتونن با تغییراتی که در دیتابیس اتفاق می افته اجرا بشن
مثل : حذف و یا اضافه کردن یک جدول،دیدگاه(ویو)،تابع و یا هر عنصری .
چند تا مثال ;
فرض کنیم نمی خواهییم در سطح برنامه نویسی هیچ عنصری[/code] از ساختار دیتابیس حذف بشه :

کد:

CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
RAISERROR ('شما مجاز به اعمال تغییر نیستید',10, 1)
ROLLBACK

می خواهییم وقتی تغییری در ساختار دیتابیس اعمال شد در یک جدولی ثبت بشه :

کد:

CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

برای امتحان :

کد:

CREATE TABLE TestTable (a int);
DROP TABLE TestTable ;
GO
SELECT * FROM ddl_log ;
GO
--Drop the trigger.
DROP TRIGGER log
ON DATABASE;
GO
--Drop table ddl_log.
DROP TABLE ddl_log;

لیست همه رویداد هایی که میشه در انتظارشون بود

[hamed_Arfaee]

تریگرها در بالاترین لایه

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


نکته : اول دیتابیسی بصورت زیر ایجاد میکنیم :
نام دیتابیس : UserManagement
و ساختار جدول :

کد:

CREATE TABLE [dbo].[Users](

[User_name] [nvarchar](50) NULL,

[hasAccess] [tinyint] NULL CONSTRAINT [DF_Users_hasAccess] DEFAULT ((1)),

[CDate] [datetime] NULL CONSTRAINT [DF_Users_CDate] DEFAULT (getdate())
) ON [PRIMARY]

هیچ دیتابیسی پاک نشه

کد:

CREATE TRIGGER trg_noDeleteDb
ON ALL SERVER
FOR Drop_Database
AS
PRINT 'Cant Drop database'

ROLLBACK

END

مثالها :
حداکثر تعداد دیتابیس های باید 10 باشد

کد:

CREATE TRIGGER trg_noMoreThan10Db
ON ALL SERVER
FOR CREATE_Database
AS
IF (SELECT COUNT(*)-4 cnt FROM
sys.databases) >=10
BEGIN
PRINT 'Cant create new database'

ROLLBACK

END

نکته : بصورت پیش فرض 4 دیتابیس سیستمی وجود دارد
هر یوزری که در جدول غیر مجازها باشد نتواند وارد شود

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

کد:

CREATE TRIGGER trg_CanLogin
ON ALL SERVER
FOR LOGON
AS
IF (SELECT COUNT(*) FROM UserManagement.dbo.Users WHERE [User_name]=original_login() AND hasAccess=0)>0
BEGIN


PRINT 'Cant Login'



ROLLBACK
END

بعد از ایجاد یوزر جدید مشخصاتش در جدول ورود مجاز به ورود ها ثبت شود

کد:

CREATE TRIGGER trg_NewUser
ON ALL SERVER
AFTER CREATE_LOGIN
AS

INSERT INTO UserManagement.dbo.Users
(

[User_name],

hasAccess
)
VALUES
(

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')

,1
)

بعد از حذف کاربر ، از جدول مجاز به ورود ها هم حذف شود

کد:

Create TRIGGER trg_DropUser
ON ALL SERVER
AFTER DROP_LOGIN
AS
DELETE FROM UserManagement.dbo.Users WHERE [User_name]=(EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'))

تا اینجا کلیات تمومه

اگر دوستان مثال کاربردی در این زمینه دارن برای تکمیل شدن بحث دریغ نکنن .

[far_222000]

حامد جان بابت آموزش بسیار بسیار خوب و واضحت تشکر می کنم.

فقط یه سؤال دارم، اونم اینه که چه مزیتی وجود داره که از "Trigger ای که روی جدول یا دیدگاه" هست باید استفاده کنیم، نه اینکه این رو بصورت یه لایه توی برناممون بنویسیم؟

مباحث امنیتی وجود داره؟ مباحث سرعت وجود داره؟ یا چیز دیگه ای؟

[hamed_Arfaee]

حامد جان بابت آموزش بسیار بسیار خوب و واضحت تشکر می کنم.
فقط یه سؤال دارم، اونم اینه که چه مزیتی وجود داره که از "Trigger ای که روی جدول یا دیدگاه" هست باید استفاده کنیم، نه اینکه این رو بصورت یه لایه توی برناممون بنویسیم؟
مباحث امنیتی وجود داره؟ مباحث سرعت وجود داره؟ یا چیز دیگه ای؟

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

1. ارتباط بادیتابیس کم میشود
   
2. وقتی مستقیم از منیجر اس کیو استفاده میکنیم هم این تریگر ها اجرا میشن
   
3. وقتی تریگری باعث تغییر در داخل دیتابیس میشه اونهم ثبت میشه
   
4. وقتی طراح دیتابیس شخصی جدا از برنامه نویس ها باشه میتونه یکپارچگی رو ایجاد کنه
   
5. وقتی چند برنامه از یک دیتابیس استفاده میکنن نیاز به ایجاد هماهنگی ثبت وقایع بینشون نیست
   

و شاید چند مزیت دیگه ولی میتونه عیب هایی هم داشته باشه

[hamed_Arfaee]

یه مطلب جدید

اگر قرار باشه برای هر جدول یک تریگر ثبت وقایع ایجاد بشه چه کسی باید این کارو انجام بده ؟

اینکارو به 3 روش میشه انجام داد :
1- خودمون دستی تریگر بنویسیم و برای هر جدول جدیدی اونو تغییر بدیم .
2- برنامه ای که خارج از دیتابیس بهش وصل بشه و این تریگر ها رو تولید کنه
3- روالی داخل دیتابیس که این کارو بکنه

من روش 3 رو عملی کردم چون می شه روش خیلی مانور داد مثلا : بعد از (ایجاد-ویرایش) جدول تریگرش هم (ایجاد-ویرایش)بشه

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

کد:

Alter FUNCTION [dbo].[GetTableTrigger]
(
    @objectid AS INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @myBoddy NVARCHAR(MAX)
    DECLARE @tblName NVARCHAR(50)
    DECLARE @myConst NVARCHAR(MAX)
   
    SET @tblName = (
            SELECT TOP 1 [name]
            FROM   sys.tables
            WHERE  [object_id] = @objectid
        )
    SELECT @myBoddy = COALESCE(@myBoddy + ' + ', '') +
           sob
    FROM   (
                SELECT ' N''' + [name] + '=''+ isnull(cast(' + [name] + ' AS nvarchar),'''')+'' , '' ' sob
               FROM   sys.[columns]
               WHERE  OBJECT_ID = @objectid
               AND  system_type_id NOT IN (241,173,165,34)
           ) AS we
   
   
    SET @myBoddy = 'INSERT INTO @NewData Select ' + @myBoddy + ' from _*tmpTable*_'
    IF (
           SELECT COUNT(*)
           FROM   sys.triggers t
           WHERE  t.name = 'Trg_' + @tblName  
       ) > 0
            SET @myConst = 'Alter TRIGGER [dbo].[Trg_' + @tblName  
         ELSE
             
         SET @myConst = 'CREATE TRIGGER [dbo].[Trg_' + @tblName  
         SET @myConst = @myConst +
        ']    ON  [dbo].[' + @tblName +
        ']   AFTER  INSERT,DELETE,UPDATE AS BEGIN '
         SET @myConst = @myConst +
        'DECLARE @NewData TABLE (tmprow NVARCHAR(MAX));DECLARE @Event varchar (20); '
   
    SET @myConst = @myConst +
        'IF (EXISTS(SELECT * FROM INSERTED) AND  EXISTS(SELECT * FROM DELETED))    BEGIN '
   
    SET @myConst = @myConst + REPLACE (@myBoddy, '_*tmpTable*_', 'INSERTED')
    SET @myConst = @myConst +
        ' SET @Event=''UPDATE''      END    else IF EXISTS(SELECT * FROM INSERTED)    BEGIN '
   
    SET @myConst = @myConst + REPLACE (@myBoddy, '_*tmpTable*_', 'INSERTED')
    SET @myConst = @myConst +
        ' SET @Event=''INSERT''            END    else IF EXISTS(SELECT * FROM DELETED)    BEGIN '
   
    SET @myConst = @myConst + REPLACE (@myBoddy, '_*tmpTable*_', 'DELETED')
    SET @myConst = @myConst + ' SET @Event=''DELETE''  END SET NOCOUNT ON; '
    SET @myConst = @myConst +
        ' INSERT INTO logs (db_table,db_command,db_commandtext)SELECT ''' + @tblName
        + ''',@Event,tmprow FROM @NewData END '
    RETURN (@myConst)
END

نکته 1: فیلدی که نوع آن عکس -باینری-ایکس ام ال هست رو ثبت نمیکنیم پس با یه شرط اونا رو حذف کردم
نکته 2:جدولی که قبلا این تریگر روش ایجاد شده براش دستور جدیدی تولید نمیشه .

چون بعد از ترکیب همه این رشته ها قابل اجرا نیستن پس با یه سلکت کرسر دونه دونشونو اجرا میکنیم :

کد:

DECLARE @myfield AS NVARCHAR(MAX)
DECLARE execTRG CURSOR  
FOR
   SELECT trg
   FROM   (
              SELECT dbo.GetTableTrigger([object_id])trg
              FROM   sys.tables t
              WHERE  [name] NOT IN ('sysdiagrams', 'logs')
          )tmptable

OPEN execTRG
FETCH NEXT FROM execTRG
INTO @myfield
WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC (@myfield)
   FETCH NEXT FROM execTRG
   INTO @myfield
END
CLOSE execTRG
DEALLOCATE execTRG

نکته : برای جدول لوگ و سیستم دیاگرام تریگر نمی سازیم .

مهمترین نکته : جدول لوگ که این اتفاقا براش می افته ساختارش تو پست اول هست .

[hamed_Arfaee]

تابع تریگر ساز رو بهینه کردم .

از این تابع بصورت عملی استفاده کردم و ایراداشو رفع کردم .
میتونم بگم 100 در 100 درست کار میکنه روی 18 تا دیتابیس هر کدوم با 450 تا جدول تست شد در مدت 2 هفته .

[hamed_Arfaee]

نکته ای که تو اجرا شدن تریگر ها جالب هست اینه که اگر تریگری باعث اجرا شدن تریگر دوم بشه آیا اون تریگر دوم اجرا میشه یا نه ؟

این تنظیم داخل خود دیتابیس هست که با تریگر های بازگشتی چه رفتاری صورت بگیره ؟!?!

Recursive triggers enabled

[hamed_Arfaee]

بغیر از ایجاد/حذف/ ویرایش تریگر ها امکان فعال/غیرفعال کردنشون هم هست که بصورت ویزاردی با راست کلیک روی ترگیر انجام میشه ولی اگر بخواهیم با کدنویسی این کار رو انجام بدیم میشه از این دستورات استفاده کرد :

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

کد:

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

مثال

کد:

DISABLE TRIGGER  [dbo].[checkBalance] ON  [dbo].[Orders];

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

کد:

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

مثال

کد:

ENABLE TRIGGER [dbo].[checkBalance] ON  [dbo].[Orders];

این مثال ها برای تریگر های جدول و دیدگاه بودن، همونطور که از ساختار دستور مشخصه برای انجام عملیات روی تریگر های دیتابیس یا اینستنس اینجوری مینویسیم :

کد:

ENABLE TRIGGER safety ON DATABASE;
DISABLE TRIGGER safety ON DATABASE;


ENABLE TRIGGER trg_noDeleteDb ON ALL SERVER;
DISABLE TRIGGER trg_noDeleteDb ON ALL SERVER;

و اگر بخواهیم همه تریگر ها (روی جدول|دیدگاه / دیتابیس / سرور) باهم فعال یا غیر فعال بشن می توان از از کلید ALL استفاده کرد .
مثال :

کد:

DISABLE Trigger ALL ON  [dbo].[Orders];
DISABLE Trigger ALL ON DATABASE;
DISABLE Trigger ALL ON ALL SERVER;

نکته1 : غیر فعال کردن تریگر اونو حذف نمیکنه، تریگر رو غیر فعال میکنه تا اجرا نشه .
نکته2 : برای اجرای دستور {فعال/غیر فعال} کردن تریگر باید سطح دسترسی ALTER رو داشته باشیم.

[hamed_Arfaee]

در پست 3 درمورد تریگر های DDL توضیح دادم 
توضیحات کامل در مورد تابع EVENTDATA :

 DDL Trigger در هر سطحی فعال (fire) شود تابع سیستمی ()EVENTDATA فراخوانی (raise) می‌شود. خروجی تابع در قالب XML است. 
عناصر کلیدی (Key Elements) تابع EVENTDATA  به شرح زیر است:
•  EventType: نوع رویدادی که باعث فراخوانی Trigger شده است.
•  PostTime: زمانی که رویداد رخ می‌دهد.
•  SPID :SPID کاربری که باعث ایجاد رویداد شده است.
•  ServerName:  نام SQL Instance  که  رویداد در آن رخ داده است.
•  LoginName: نام Login که عمل مربوط به وقوع رویداد را اجرا می‌کند.
•  UserName: نام User که عمل مربوط به وقوع رویداد را اجرا می‌کند.
•  DatabaseName: نام Database که رویداد در آن رخ می‌دهد.
•  ObjectType: نوع Object که اصلاح، حذف و یا ایجاد شده است.
•  ObjectName: نام Object که اصلاح، حذف و یا ایجاد شده است.
•  TSQLCommand: دستور T-SQL که اجرا شده و باعث اجرا شدن Trigger شده است.  

منبع: dotnettips.info