آموزش جامع SQL Server (جلسه ۲۵:Triggerها)
1398/04/10 14:32 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۲۵:Triggerها)

Triggerها در SQL Server در واقع storred procedureهایی هستند که به صورت خودکار در پاسخ به شیء پایگاه داده، پایگاه داده و رویدادها یا Eventهای سرور اجرا می‌شوند. SQL Serverسه نوع Trigger فراهم می‌کند:

  • Triggerهای زبان دست‌کاری داده (DML) که به صورت خودکار در پاسخ به رویدادهای UPDATE، INSERT و DELETE برای جدول فراخوانی می‌شوند.

  • Triggerهای زبان تعریف داده (DDL) که در پاسخ به دستورهای ALTER، CREATE و DROP اجرا می‌شوند. Triggerهای DDL همچنین در پاسخ به برخی از Stored procedureهای سیستمی که عملیات شبیه به DDL انجام می‌دهند نیز اجرا می‌شود.

  • Triggerهای Logon که در پاسخ به رویدادهای LOGON اجرا می‌شوند.

در این جلسه، چگونگی استفاده مؤثر از Triggerها در SQL Server را خواهید آموخت. موارد مورد بحث عبارت‌اند از:

  • ایجاد یک Trigger در SQL Server: چگونگی ایجاد یک Trigger در پاسخ به رویدادهای insert و delete را نشان می‌دهد.

  • ایجاد یک Trigger به نام INSTEAD OF: در مورد Trigger به نام INSTEAD OF و کاربردهای عملی و مفید آن خواهید آموخت.

  • ایجاد یک Trigger از نوع DDL: چگونگی ایجاد یک Trigger از نوع DDL برای نظارت بر تغییرات اعمال شده بر ساختارهای اشیاء پایگاه داده مانند جداول، viewها و indexها را خواهید آموخت.

  • غیر فعال کردن Triggerها: چگونگی غیر فعال کردن موقت یک جدول را خواهید آموخت، تا وقتی رویدادهای مرتبط با آن اتفاق می‌افتند، اجرا نشود.

  • فعال کردن Triggerها: چگونگی فعال کردن یک Trigger را نشان می‌دهد.

  • نظارت بر تعریف یک Trigger: روش‌های مختلف نظارت بر یک Trigger را معرفی می‌کند.

  • لیست کردن تمام Triggerها در SQL Server: چگونگی لیست کردن تمام Triggerها در SQL Server با استفاده از نوشتن query برای به دست آوردن داده‌ها از view به نام triggers را نشان می‌دهد.

  • حذف Triggerها: چگونگی حذف یک یا چند Trigger موجود را نشان می‌دهد.

در ادامه تمام این مراحل را به صورت کامل و مفصل توضیح خواهیم داد. با ما همراه باشید!

دستور CREATE TRIGGER در SQL Server

دستور CREATE TRIGGER به کاربر امکان می‌دهد تا یک Trigger جدید ایجاد کند که هر موقع یک رویداد مانند DELETE، INSERT یا UPDATE برای یک جدول اتفاق افتادند، به صورت خودکار اجرا شود.

کد زیر syntax یک دستور CREATE TRIGGER را نشان می‌دهد:

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER  {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

در این syntax:

  • Schema_name نام شمایی است که Trigger جدید به آن تعلق دارد. نام شما اختیاری است.

  • Trigger_name نام تعریف شده توسط کاربر برای Trigger جدید است.

  • Table_name جدولی است که Trigger برای آن اعمال می‌شود.

  • رویدادها در داخل دستور AFTER لیست می‌شوند. رویداد می‌تواند UPDATE، INSERT یا DELETE باشد. یک Trigger می‌تواند در پاسخ به یک یا تعداد بیشتری عمل مرتبط با جدول اجرا شود.

  • آپشن NOT FOR REPLICATION به SQL Server دستور می‌دهد که وقتی عملیات تغییر یا اصلاح داده (به عنوان بخشی از فرآیند تکرار) انجام می‌شود، Trigger را اجرا نکند.

  • Sql_statements یک یا تعداد بیشتری Transact_SQL است که برای انجام اعمال در زمانی که یک رویداد رخ می‌دهد، مورد استفاده قرار می‌گیرد.

جداول مجازی (Virtual) برای Triggerها: INSERTED و DELETED

SQL Server دو جدول مجازی فراهم می‌کند که مخصوصاً برای Triggerها طراحی شده‌اند و نام آن‌ها جداول INSERTED و DELETED است. SQL Server از این جداول در قبل و بعد از اتفاق افتادن رویداد، برای به دست آوردن داده از ردیف اصلاح شده استفاده می‌کند.

جدول زیر محتوای جداول INSERTED و DELETED را قبل و بعد از وقوع هر رویداد نشان می‌دهد:

رویداد DML

محتوای جدول INSERTED

محتوای جدول DELETED

INSERT

ردیف‌هایی که قرار است وارد شوند

خالی

UPDATE

ردیف‌های جدید اصلاح شده توسط دستور UPDATE

ردیف‌های موجود اصلاح شده توسط دستور UPDATE

DELETE

خالی

ردیف‌هایی که باید حذف شوند

مثال CREATE TRIGGER در SQL Server

اجازه دهید مثالی از ایجاد یک Trigger جدید انجام دهیم. در این مثال از جدول production.products از پایگاه داده نمونه برای نمایش استفاده می‌کنیم.

جدول production.products

۱)ایجاد یک جدول برای log یا لیست کردن تغییرات

کد زیر یک جدول به نام production.product_audits برای ثبت اطلاعات ایجاد می‌کند که وقتی یک رویداد INSERT یا DELETE برای جدول production.products اتفاق می‌افتند، وارد عمل می‌شود:

CREATE TABLE production.product_audits(
    change_id INT IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT NOT NULL,
    list_price DEC(10,2) NOT NULL,
    updated_at DATETIME NOT NULL,
    operation CHAR(3) NOT NULL,
    CHECK(operation = 'INS' or operation='DEL')
);

جدول production.product_audits دارای تمام ستون‌های جدول production.products می‌باشد. علاوه بر این، دارای دو ستون دیگر برای ثبت تغییراتی همچون operation، updated_at و change_id نیز هست.

۲)ایجاد یک Trigger از نوع DML

ابتدا، برای ایجاد یک Trigger جدید، باید نام Trigger و شمایی که Trigger به آن تعلق دارد را در داخل دستور CREATE TRIGGER مشخص کنیم:

CREATE TRIGGER production.trg_product_audit

سپس، نام جدولی که در صورت اتفاق افتادن رویداد، یک Trigger برای آن اجرا می‌شود را در داخل دستور ON مشخص می‌کنیم:

ON production.products

بعد، یک یا تعداد بیشتری رویداد که باعث فراخوانی Trigger می‌شوند را در داخل دستور AFTER می‌نویسیم:

AFTER INSERT, DELETE

بدنه Trigger با کلیدواژه AS شروع می‌شود:

AS
BEGIN

پس از آن داخل بدنه Trigger، کد SET NOCOUNT را روی ON تنظیم می‌کنیم تا هر موقع Trigger اجرا شد، پیغام ردیف‌های تحت تأثیر قرار گرفته بازگردانده نشود.

SET NOCOUNT ON;

هر موقع یک ردیف درون جدول production.products اضافه یا از آن حذف می‌شود، Trigger یک ردیف در داخل جدول production.product_audits ایجاد می‌کند.

داده‌ها برای insert شدن از طریق جداول INSERTED و DELETED و به وسیله عملگر UNION ALL به دست می‌آیند:

INSERT INTO
    production.product_audits
        (
            product_id,
            product_name,
            brand_id,
            category_id,
            model_year,
            list_price,
            updated_at,
            operation
        )
SELECT
    i.product_id,
    product_name,
    brand_id,
    category_id,
    model_year,
    i.list_price,
    GETDATE(),
    'INS'
FROM
    inserted AS i
UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        getdate(),
        'DEL'
    FROM
        deleted AS d;

کد زیر همه قطعات را کنار یکدیگر قرار می‌دهد:

CREATE TRIGGER production.trg_product_audit
ON production.products
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO production.product_audits(
        product_id, 
        product_name,
        brand_id,
        category_id,
        model_year,
        list_price, 
        updated_at, 
        operation
    )
    SELECT
        i.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        i.list_price,
        GETDATE(),
        'INS'
    FROM
        inserted i
    UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        GETDATE(),
        'DEL'
    FROM
        deleted d;
END

در آخر، کل کد را برای ایجاد Trigger اجرا می‌کنیم. وقتی Trigger ایجاد شد، می‌توانید آن را در زیر فولدر Triggerها که در تصویر زیر مشاهده می‌کنید، بیابید:

وقتی Trigger ایجاد شد، می‌توانید آن را در زیر فولدر Triggerها بیابید

۳)تست trigger

کد زیر یک ردیف جدید در داخل جدول production.products ایجاد می‌کند:

INSERT INTO production.products(
    product_name, 
    brand_id, 
    category_id, 
    model_year, 
    list_price
)
VALUES (
    'Test product',
    1,
    1,
    2018,
    599
);

به خاطر رویداد DELETE، یک trigger به نام produuction.trg_product_audit از جدول production.products اجرا شده است.

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

SELECT 
    * 
FROM 
    production.product_audits;

خروجی به این شکل است:

نتایج

کد زیر یک ردیف از جدول production.products حذف می‌کند:

DELETE FROM 
    production.products
WHERE 
    product_id = 322;

همان‌طور که انتظار می‌رفت، trigger اجرا شد و ردیف حذف شده را به جدول production.product_audits را اضافه کرد:

SELECT 
    * 
FROM 
    production.product_audits;

تصویر زیر خروجی را نشان می‌دهد:

نتایج

در نتیجه، چگونگی ایجاد یک trigger در پاسخ به یک یا چند رویداد مانند insert و delete را آموختید.

Trigger به نام INSTEAD OF در SQL Server

INSTEAD OF یک trigger است که به شما اجازه می‌دهد از یک دستور DELETE، INSERT یا UPDATE برای یک جدول یا از یک view بگذرید و در عوض دستورات دیگر تعریف شده در trigger را اجرا کنید.

در واقع عملیات وارد کردن، حذف یا به روز رسانی اصلاً اتفاق نخواهند افتاد.

به عبارت دیگر، یک trigger به نام ISNTEAD OF از یک دستور DML می‌گذرد و بقیه دستورات را اجرا می‌کند.

Syntax یا سینتکس از trigger به نام INSTEAD OF در SQL Server

کد زیر syntax چگونگی ایجاد یک trigger به نام INSTAED OF را نشان می‌دهد:

CREATE TRIGGER [schema_name.] trigger_name
ON {table_name | view_name }
INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] }
AS
{sql_statements}

در این syntax:

  • ابتدا، نام trigger و در صورت تمایل (اختیاری) نام شمایی که trigger به آن تعلق دارد را درون دستور CREATE TRIGGER مشخص می‌کنیم.

  • دوم، نام جدول یا view که trigger به آن مرتبط است را مشخص می‌کنیم.

  • سوم، رویدادی مانند DELETE، INSERT یا UPDATE که باعث اجرای trigger می‌شود را در دستور INSTEAD OF مشخص می‌کنیم. Trigger ممکن است در پاسخ به یک یا چند رویداد فراخوانی شود.

  • چهارم، بدنه trigger را بعد از کلیدواژه AS قرار می‌دهیم. بدنه یک trigger می‌تواند شامل یک یا چند دستور Transact_SQL باشد.

مثال trigger به نام INSTEAD OF در SQL Server

یک مثال معمول استفاده از trigger به نام INSTEAD OF برای بازنویسی یک عملیات update، insert یا delete در یک view است.

فرض کنید یک برنامه نیاز دارد برندهای جدیدی به جدول production.brands اضافه کند. به هر حال، برندهای جدید باید در یک جدول دیگر به نام production.brand_approvals ذخیره شوند تا پیش از اینکه وارد جدول production.brands شوند، تأیید شوند.

برای انجام این امر، یک view به نام production.vw_brands برای برنامه ایجاد می‌کنیم تا برندهای جدید را وارد کند. اگر برندها درون view وارد شوند، یک trigger به نام INSTEAD OF اجرا می‌شود تا برندها را به جدول production.brand_approvals اضافه کند.

تصویر زیر، این فرآیند را نشان می‌دهد:

فرآیند INSTEAD OF

این نمودار برای ساده‌تر شدن نمایش فرآیند، نام شمای همه اشیاء پایگاه داده را نشان نمی‌دهد.

کد زیر یک جدول جدید به نام production.brand_approvals برای مرتب‌سازی برندهای تأیید شده در صف انتظار، ایجاد می‌کند:

CREATE TABLE production.brand_approvals(
    brand_id INT IDENTITY PRIMARY KEY,
    brand_name VARCHAR(255) NOT NULL
);

کد زیر یک view جدید به نام production.vw_brands برای جداول production.brands و production.brand_approvals ایجاد می‌کند:

CREATE VIEW production.vw_brands 
AS
SELECT
    brand_name,
    'Approved' approval_status
FROM
    production.brands
UNION
SELECT
    brand_name,
    'Pending Approval' approval_status
FROM
    production.brand_approvals;

وقتی یک ردیف به view به نام production.vw_brands اضافه شد، باید با استفاده از trigger به نام INSTEAD OF که در زیر مشاهده می‌کنید، آن را به سمت جدول production.brand_approvals هدایت کنیم:

CREATE TRIGGER production.trg_vw_brands 
ON production.vw_brands
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO production.brand_approvals ( 
        brand_name
    )
    SELECT
        i.brand_name
    FROM
        inserted i
    WHERE
        i.brand_name NOT IN (
            SELECT 
                brand_name
            FROM
                production.brands
        );
END

در صورتی که نام برند از قبل در جدول production.brands وجود نداشته باشد، Trigger نام برند جدید را به جدول production.brand_approvals اضافه می‌کند.

اجازه دهید یک برند جدید به view به نام production.vw_brands اضافه کنیم:

INSERT INTO production.vw_brands(brand_name)
VALUES('Eddy Merckx');

این دستور INSERT باعث اجرای trigger به نام INSTEAD OF و اضافه شدن یک ردیف جدید به جدول production.brand_approvals می‌شود.

اگر یک query برای به دست آوردن داده‌ها از جدول production.vw_brands بنویسید، مشاهده خواهید کرد که یک ردیف جدید ظاهر می‌شود:

SELECT
 brand_name,
 approval_status
FROM
 production.vw_brands;

نتایج

کد زیر محتوای جدول production.brand_approvals را نمایش می‌دهد:

SELECT 
 *
FROM 
 production.brand_approvals;

نتایج

به این صورت، راجع به trigger به نام INSTEAD OF و چگونگی ایجاد آن برای اضافه کردن داده به یک جدول مورد نظر از طریق یک view را کاملاً آموختید.

Triggerهای DDL در SQL Server

Triggerهای DDL در SQL Server به رویدادهای سرور یا پایگاه داده پاسخ می‌دهند نه به اصلاحات داده‌های جدول.

این رویدادها توسط دستورات Transact_SQL ایجاد می‌شوند که معمولاً با یکی از کلیدواژه‌های REVOKE، DENY، GRANT، DROP، ALTER، CREATE یا UPDATE STATISTICS شروع می‌شوند.

برای مثال، می‌توانید یک trigger از نوع DDL بنویسید تا هر موقع کاربر یکی از دستورات CREATE TABLE یا ALTER TABLE را وارد کرد، trigger آن را ثبت کند.

Triggerهای DDL در موارد زیر مفید هستند:

  • ثبت تغییرات در شمای پایگاه داده.

  • جلوگیری از برخی تغییرات خاص برای شمای پایگاه داده.

  • پاسخ به یک تغییر در شمای پایگاه داده.

کد زیر syntax ایجاد یک trigger از نوع DDL را نشان می‌دهد:

CREATE TRIGGER trigger_name
ON { DATABASE |  ALL SERVER}
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {sql_statement}
Trigger_name

نام تعریف شده توسط کاربر برای trigger را پس از کلیدواژه‌های CREATE TRIGGER مشخص می‌کند. توجه کنید که لازم نیست برای یک trigger از نوع DDL شما مشخص کنید، چون trigger به یک جدول یا view از یک پایگاه داده مربوط نیست.

DATABASE | ALL SERVER

اگر trigger به رویدادهای مرتبط با پایگاه داده پاسخ می‌دهد از DATABASE و اگر به رویدادهای مرتبط با سرور پاسخ می‌دهد از ALL SERVER استفاده می‌کنیم.

ddl_trigger_option

این کد ENCRYPTION و / یا دستور EXECUTE AS را مشخص می‌کند. ENCRYOTION تعریف trigger را رمزگذاری می‌کند. EXECUTE AS شرایط امنیتی که trigger تحت آن‌ها اجرا می‌شود را تعریف می‌کند.

Event_type | event_group

دستور event_type یک رویداد DDL را نشان می‌دهد که باعث اجرا شدن trigger می‌شود (رویدادی مانند ALTER_TABLE، CREATE_TABLE و غیره).

دستور event_group گروهی از رویدادهای event_group مانند DDL_TABLE_EVENTS است.

یک trigger می‌تواند مرتبط با یک یا تعداد بیشتری رویداد یا گروه‌های رویدادها باشد.

مثال ایجاد یک trigger از نوع DDL در SQL Server

فرض کنید می‌خواهید تمام تغییرات اعمال شده در index پایگاه داده را بگیرید تا بتوانید بهتر عملکرد سرور پایگاه داده (که مرتبط با این تغییرات index است) را نظارت کنید.

ابتدا، یک جدول جدید به نام index_logs ایجاد می‌کنیم تا تغییرات index را لیست کند:

CREATE TABLE index_logs (
    log_id INT IDENTITY PRIMARY KEY,
    event_data XML NOT NULL,
    changed_by SYSNAME NOT NULL
);
GO

سپس، یک trigger از نوع DDL ایجاد می‌کنیم تا تغییرات index را پیگیری کرده و داده‌های رویدادها را به جدول index_logs وارد کند:

CREATE TRIGGER trg_index_changes
ON DATABASE
FOR 
    CREATE_INDEX,
    ALTER_INDEX, 
    DROP_INDEX
AS
BEGIN
    SET NOCOUNT ON;
 
    INSERT INTO index_logs (
        event_data,
        changed_by
    )
    VALUES (
        EVENTDATA(),
        USER
    );
END;
GO

در بدنه trigger، از تابع ()EVENTDATA استفاده می‌کنیم که اطلاعاتی راجع به رویدادهای سرور یا پایگاه داده را بازمی‌گرداند. این تابع فقط در triggerهای DDL یا LOGON قابل استفاده است.

سپس، indexها را برای ستون‌های first_name و last_name از جدول sales.customers ایجاد می‌کنیم:

CREATE NONCLUSTERED INDEX nidx_fname
ON sales.customers(first_name);
GO
 
CREATE NONCLUSTERED INDEX nidx_lname
ON sales.customers(last_name);
GO

پس از آن، داده‌های را از جدول index_changes به دست می‌آوریم تا بررسی کنیم که آیا رویداد ایجاد کننده index به درستی و مناسب توسط trigger گرفته شده است یا خیر:

SELECT 
    *
FROM
    index_logs;

خروجی به این شکل است:

نتایج

اگر روی یکی از سلول‌های ستون event_data کلیک کنید، می‌توانید داده‌های XML از رویداد را به شکل زیر مشاهده کنید:

نتایج

در نتیجه، چگونگی ایجاد یک trigger از نوع DDL که به یک یا تعداد بیشتری رویداد DDL پاسخ می‌دهد را نیز آموختید.

دستور DISABLE TRIGGER در SQL Server

گاهی، برای عیب‌یابی یا بازیابی داده‌ها، ممکن است بخواهید موقتاً یک trigger را غیر فعال کنید. برای این منظور، می‌توانید از دستور DISABLE TRIGGER استفاده کنید:

DISABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]

 در این syntax:

  • ابتدا، نام شمایی که trigger به آن تعلق دارد و نام trigger که می‌خواهید غیر فعال شود را پس از کلیدواژه‌های DISABLE TRIGGER مشخص می‌کنیم.

  • سپس، نام جدول یا view که trigger به آن‌ها متصل است (در صورتی که trigger از نوع DML باشد) را مشخص می‌کنیم. اگر trigger از نوع پایگاه داده DDL است از DATABASE یا اگر از نوع سرور DDL است از SERVER استفاده می‌کنیم.

مثال DISABLE TRIGGER در SQL Server

کد زیر یک جدول جدید به نام sales.members برای مثال ایجاد می‌کند:

CREATE TABLE sales.members (
    member_id INT IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    member_level CHAR(10) NOT NULL
);

کد زیر یک trigger ایجاد می‌کند که هر موقع یک ردیف جدید به جدول sales.members اضافه شد، اجرا می‌شود. برای هدف نمایش، trigger فقط یک پیغام ساده بازمی‌گرداند.

CREATE TRIGGER sales.trg_members_insert
ON sales.members
AFTER INSERT
AS
BEGIN
    PRINT 'A new member has been inserted';
END;

کد زیر یک ردیف جدید به جدول sales.members اضافه می‌کند:

INSERT INTO sales.members(customer_id, member_level)
VALUES(1,'Silver');

به خاطر رویداد INSERT، trigger اجرا شد و پیام زیر را نمایش داد:

A new member has been inserted

برای غیر فعال کردن trigger به نام sales.trg_members_insert، می‌توانید از کد DISABLE TRIGGER استفاده کنید:

DISABLE TRIGGER sales.trg_members_insert 
ON sales.members;

حالا اگر یک ردیف جدید به جدول Sales.members اضافه کنید، trigger اجرا نخواهد شد:

INSERT INTO sales.members(customer_id, member_level)
VALUES(2,'Gold');

این بدان معنا است که trigger غیر فعال شده است.

توجه کنید که تعریف trigger هنوز در جدول وجود دارد. اگر trigger را در (SQL Server Managenent Studio (SSMS مشاهده کنید، متوجه خواهید شد که یک آیکن ضربدر قرمز در کنار نام trigger غیرفعال شده قرار دارد:

اگر trigger را در (SQL Server Managenent Studio (SSMS مشاهده کنید، متوجه خواهید شد که یک آیکن ضربدر قرمز در کنار نام trigger غیرفعال شده قرار دارد

غیر فعال‌سازی تمام triggerها در یک جدول

برای غیرفعال کردن همه triggerها در یک جدول، می‌توانید از کد زیر استفاده کنید:

DISABLE TRIGGER ALL ON table_name;

در این کد، شما فقط باید نام جدول را برای غیر فعال‌سازی همه triggerهایی که به جدول تعلق دارند، مشخص کنید.

کد زیر یک trigger جدید در جدول Sales.members ایجاد می‌کند که بعد از رویداد delete اجرا می‌شود:

CREATE TRIGGER sales.trg_members_delete
ON sales.members
AFTER DELETE
AS
BEGIN
    PRINT 'A new member has been deleted';
END;

برای غیر فعال کردن تمام triggerها در جدول sales.members، از کد زیر استفاده می‌کنیم:

DISABLE TRIGGER ALL ON sales.members;

تصویر زیر وضعیت تمام triggerهایی که به جدول sales.members تعلق  دارند را نشان می‌دهد:

وضعیت تمام triggerهایی که به جدول sales.members تعلق  دارند

غیر فعال کرد همه triggerها در یک پایگاه داده

برای غیر فعال کردن تمام triggerها در پایگاه داده فعلی، می‌توانید از کد زیر استفاده کنید:

DISABLE TRIGGER ALL ON DATABASE;

به این شکل، چگونگی استفاده از دستور DISABLE TRIGGER برای غیر فال کردن یک trigger را نیز آموختید.

دستور ENABLE TRIGGER در SQL Server

دستور ENABLE TRIGGER به شما اجازه می‌دهد یک trigger را فعال کنید، تا هر موقع یک رویداد رخ داد، trigger اجرا شود.

کد زیر syntax دستور ENABLE TRIGGER را نشان می‌دهد:

ENABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]

در این syntax:

  • ابتدا، نام trigger که می‌خواهید فعال شود را مشخص می‌کنید. در صورت تمایل، می‌توانید نام شمایی که trigger به آن تعلق دارد را نیز مشخص کنید.

  • سپس، جدولی که trigger به آن تعلق دارد (اگر trigger از نوع DML است) را مشخص می‌کنید. اگر trigger از نوع پایگاه داده DDL است از DATABASE و اگر از نوع سرور DDL است از SERVER استفاده کنید.

مثال ENABLE TRIGGER در SQL Server

از جدول sales.members ایجاد شده در قسمت قبلی (غیر فعال کردن trigger) برای این مثال استفاده می‌کنیم.

برای فعال‌سازی trigger به نام sales.trg_members_insert، می‌توانید از دستور زیر استفاده کنید:

ENABLE TRIGGER sales.trg_members_insert
ON sales.members;

وقتی trigger فعال شد، می‌توانید وضعیت آن را از طریق SSMS بررسی کنید که در تصویر زیر مشاهده می‌کنید:

وقتی trigger فعال شد، می‌توانید وضعیت آن را از طریق SSMS بررسی کنید

فعال کردن همه triggerها در یک جدول

برای فعال کردن همه triggerهای یک جدول، می‌توانید از دستور زیر استفاده کنید:

ENABLE TRIGGER ALL ON table_name;

در این syntax، فقط باید نام جدولی که می‌خواهید تمام triggerهای مرتبط با آن فعال شوند را مشخص کنید.

برای مثال، برای فعال کردن همه triggerهای جدول sales.members، می‌توانید از کد زیر استفاده کنید:

ENABLE TRIGGER ALL ON sales.members;

تصویر زیر وضعیت تمام triggerهای تعریف شده برای جدول sales.members را نشان می‌دهد:

وضعیت تمام triggerهای تعریف شده برای جدول sales.members

فعال کردن همه triggerهای یک پایگاه داده

برای فعال کردن همه triggerهای موجود در پایگاه داده فعلی، می‌توانید از کد زیر استفاده کنید:

ENABLE TRIGGER ALL ON DATABASE; 

به این شکل، چگونگی استفاده از دستور ENABLE TRIGGER برای فعال سازی trigger یک جدول را آموختید. همچنین چگونگی فعال سازی تمام triggerهای یک جدول و یک پایگاه داده را نیز یاد گرفتید.

تعریف view trigger  در SQL Server

می‌توانید تعریف یک trigger را با نوشتن query برای به دست آوردن داده‌های view به نام sys.sql_modules به دست آورید:

SELECT 
    definition   
FROM 
    sys.sql_modules  
WHERE 
    object_id = OBJECT_ID('sales.trg_members_delete'); 

خروجی به این شکل است:

نتایج

در این query، نام trigger که می‌خواهید تعریف آن را به دست آورید را به تابع ()OBJECT_ID در دستور WHERE ارسال کردیم.

به دست آوردن تعریف trigger با استفاده از تابع OBJECT_DEFINITION

می‌توانید تعریف یک trigger را با استفاده از تابع OBJECT_DEFINITION به دست آورید:

SELECT 
    OBJECT_DEFINITION (
        OBJECT_ID(
            'sales.trg_members_delete'
        )
    ) AS trigger_definition;

در این query، نام trigger را به تابع OBJECT_ID ارسال کردیم تا ID متعلق به trigger را به دست آوریم. سپس، از تابع ()OBJECT_DEFINITION برای به دست آوردن متن منبع Transact_SQL از تعریف یک trigger را بر اساس ID آن به دست آوریم.

به دست آوردن تعریف با استفاده از Stored procedure به نام sp_helptext

ساده‌ترین روش برای به دست آوردن تعریف یک trigger، استفاده از stored procedure به نام sp_helptext به شکل زیر است:

EXEC sp_helptext 'sales.trg_members_delete' ;

Stored procedure به نام sp_helptext تعریف مورد استفاده برای ایجاد یک شیء (که در این مثال یک trigger است) را بازمی‌گرداند.

به دست آوردن تعریف trigger با استفاده از SSMS

برای مشاهده تعریف یک trigger از نوع DML

  1. اول، در OBJECT EXPLORER به پایگاه داده متصل شوید و لیست آن را باز کنید.

  2. دوم، لیست پایگاه داده و جدولی که حاوی trigger مورد نظر شما است را باز کنید.

  3. سوم، فولدر triggerها را باز کنید، روی trigger مورد نظرتان کلیک راست کنید و سپس روی Modify کلیک کنید. تعریف trigger در پنجره query ظاهر می‌شود.

به دست آوردن تعریف trigger با استفاده از SSMS

در نتیجه، روش‌های مختلف مشاهده تعریف یک trigger را آموختید.

لیست کردن تمام triggerها در SQL Server

برای لیست کردن تمام triggerها در SQL Server، داده‌های view به نام Sys.triggers را به دست می‌آوریم:

SELECT  
    name,
    is_instead_of_trigger
FROM 
    sys.triggers  
WHERE 
    type = 'TR';

تصویر زیر خروجی را نشان می‌دهد:

نتایج

دستور DROP TRIGGER در SQL Server

دستور DROP TRIGGER در SQL Server یک یا تعداد بیشتری trigger را از پایگاه داده حذف می‌کند. کد زیر syntax دستور DROP TRIGGER را نشان می‌دهد که triggerهای DML را حذف می‌کند:

DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ];

در این syntax:

  • IF EXISTS به صورت شرطی trigger را فقط زمانی حذف می‌کند که از قبل وجود داشته باشد.

  • Schema_name نام شمایی است که trigger نوع DML به آن تعلق دارد.

  • Trigger_name نام trigger است که می‌خواهید حذف شود.

اگر می‌خواهید چندین trigger را یک باره حذف کنید، باید triggerها را با استفاده از ویرگول از هم جدا کنید.

برای حذف یک یا چند trigger از نوع DDL، از فرم زیر برای دستور DROP TRIGGER استفاده می‌کنیم:

DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON { DATABASE | ALL SERVER };

در این syntax:

  • DATABASE نشان می‌دهد که دامنه trigger از نوع DDL پایگاه داده فعلی را شامل می‌شود.

  • ALL SERVER نشان می‌دهد که دامنه trigger از نوع DDL سرور فعلی را شامل می‌شود.

برای حذف یک trigger رویداد از نوع LOGON، می‌توانید از syntax زیر استفاده کنید:

DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON ALL SERVER;

توجه کنید که وقتی یک جدول را حذف می‌کنید، همه triggerهای مرتبط با آن جدول نیز به صورت خودکار حذف خواهند شد.

مثال‌های DROP TRIGGER در SQL Server

الف) مثال حذف یک trigger از نوع DML

کد زیر یک trigger از نوع DML به نام sales.trg_member_insert را نشان می‌دهد:

DROP TRIGGER IF EXISTS sales.trg_member_insert;
ب) مثال حذف یک trigger از نوع DDL

کد زیر trigger به نام trg_index_changes را حذف می‌کند:

DROP TRIGGER IF EXISTS trg_index_changes;

به این صورت، چگونگی حذف یک trigger با استفاده از دستور DROP TRIGGER را نیز آموختید.

خلاصه

در این جلسه مبحث triggerها را شروع کردیم و تعریف کردیم که trigger چیست.

همچنین چگونگی ایجاد یک trigger، ایجاد trigger به نام INSTEAD OF، ایجاد trigger از نوع DDL، غیرفعال و فعال کردن triggerها، مشاهده تعریف یک trigger، لیست کردن تمام triggerها و حذف trigger را نیز به صورت کامل و مثال محور آموختید.

در جلسه بعدی مبحث توابع جمعی را بیان خواهیم کرد.

با MUG همراه باشید!

 منبع: sqlservetutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۳۱: توابع رشته‌ای – بخش ۳)
آموزش جامع SQL Server (جلسه ۳۱: توابع رشته‌ای – بخش ۳)

در جلسه قبل بخش ۲ مطالب مبحث توابع رشته‌ای یا string را به صورت کامل توضیح دادیم. و ...

آموزش جامع SQL Server (جلسه ۳۰: توابع رشته‌ای – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۰: توابع رشته‌ای – بخش ۲)

در جلسه قبل، مبحث توابع رشته‌ای یا String را آغاز کردیم. به دلیل ازدیاد این توابع و برای ...

با Visual Studio Code’s Live Share گروهی برنامه‌نویسی کنیم!
با Visual Studio Code’s Live Share گروهی برنامه‌نویسی کنیم!

چه در مراحل اولیه یادگیری یک زبان برنامه‌نویسی باشید یا یک کد نویس با تجربه و ماهر باشید، ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات