آموزش PL/SQL قسمت دوازدهم TRIGGER در اوراکل PL/SQL

در این قسمت TRIGGER و روش استفاده از آن را توضیح می دهیم. TRIGGER یک بلاک برنامه ذخیره شده در دیتابیس اوراکل است که همزمان با رخدادهای خاص به صورت اتوماتیک اجرا (FIRE) می شود.

رخدادهایی که سبب اجرای یک TRIGGER می شوند عبارتند از:

1.رخداد دستورات DML

2.رخداد دستورات DDL

3.رخدادهای خاص در دیتابیس مانند LOGON، LOGOFF، STARTUP و SHUTDOWN

در چه سطحی می توان TRIGGER را تعریف نمود؟

-جدول

-VIEW

-SCHEMA

-دیتابیس

چرا از TRIGGER استفاده می شود؟

-جلوگیری از تراکنش های اشتباه

-ایجاد امنیت

-انجام REPLICATION

-اطلاعات مربوط به دسترسی یا دستکاری ذخیره شوند(auditing).

-تولید برخی مقدارهای خاص به صورت اتوماتیک

 

1.ساخت TRIGGER برای دستورات DML

در ادامه فرمت کلی ساخت TRIGGER برای دستورات DML را می بینید.

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

با استفاده ازBEFORE و یا AFTER  می توان مشخص نمود که زمان اجرا شدن TRIGGER قبل یا بعد از رخداد باشد. عبارت INSTEAD OF برای ساخت TRIGGER بر روی VIEW استفاده می شود.

در قسمت  {INSERT [OR] | UPDATE [OR] | DELETE} عملیات DML مورد نظر را مشخص می کنیم و عبارت [OF col_name] نام ستونی که دستکاری می شود را مشخص می کند. می توان یک یا چند DML را مشخص نمود.

قسمت ON table_name نام جدولی است که TRIGGER برای آن اجرا می شود

با استفاده از [REFERENCING OLD AS o NEW AS n] یعنی n و o می توان به مقدارهای قبل و بعد از دستور DML اشاره نمود. اگر از این عبارت استفاده نشود از  OLD و NEW می توان استفاده کرد. همچنین توجه شود که این مقادیر فقط برای TRIGGERهای از نوع ROW-LEVEL قابل استفاده هستند.

اگر از عبارت [FOR EACH ROW]  استفاده گردد TRIGGER برای هر سطر که دستکاری شده است اجرا می شود (ROW-LEVEL TRIGGER). در غیر این صورت TRIGGER فقط یکبار برای تمام دستور DML اجرا می شود(TABLE-LEVEL TRIGGER).

اگر TRIGGER به صورت ROW-LEVEL تعریف شود دستور شرطی WHEN برای هر سطر بررسی می شود در غیر این صورت نمی توان از WHEN استفاده تمود.

نکته: نمی توان برای OBJECTهایی که کاربر SYS و SYSTEM مالک آنها هستند TRIGGER تعریف کرد.

مثال 1: یک TRIGGER به صورت ROW-LEVEL برای هرگونه عملیات DML برای جدول costumers بسازید.

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers

FOR EACH ROW

WHEN (NEW.ID > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :NEW.salary – :OLD.salary;

dbms_output.put_line(‘Old salary: ‘ || :OLD.salary);

dbms_output.put_line(‘New salary: ‘ || :NEW.salary);

dbms_output.put_line(‘Salary difference: ‘ || sal_diff);

END;

/

با اجرای دستور DML زیر این TRIGGER را FIRE می کنیم.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (7, ‘Kriti’, 22, ‘HP’, 7500.00 );

خروجی:

Old salary:

New salary: 7500

Salary difference:

مقدار OLD برای دستور قبلی برابر با NULL بود. در ادامه، یک دستور UPDATE اجرا می کنیم.

UPDATE customers

SET salary = salary + 500

WHERE id = 2;

خروجی

Old salary: 1500

New salary: 2000

Salary difference: 500

 

نکته: توجه شود که از علامت : قبل از دستیابی به مقدارهای NEW و OLD استفاده می شود.

مثال 2: هر عمل INSERT در جدول ORDERS توسط چه شخصی و در چه تاریخی انجام می شود؟ این اطلاات را در همان سطر از جدول و در ستونهای CREATED_BY و CREATE_DATE ذخیره کنید.

CREATE OR REPLACE TRIGGER orders_before_insert

BEFORE INSERT

   ON orders

   FOR EACH ROW

DECLARE

   v_username varchar2(10);

BEGIN

   — Find username of person performing INSERT into table

   SELECT user INTO v_username   FROM dual;

   — Update create_date field to current system date

   :new.create_date := sysdate;

   — Update created_by field to the username of the person performing the INSERT

   :new.created_by := v_username;

END;

/

 

نکته 1: در داخل برنامه TRIGGER از نوع  BEFORE می توان مقدارهای NEW را عوض کرد ولی مقدارهای OLD قابل تغییر نیستند. برای مثال می توان از دستور زیر استفاده نمود:

:new.salary:=200;

نکته 2: در داخل TRIGGER های از نوع AFTER هیچ کدام از مقدارهای NEW و OLD قابل تغییر نیستند.

نکته 3: اگر می خواهیم در داخل برنامه TRIGGER که برای جدول A تعریف شده است، جدول A را پرس و جو کنیم می بایست از TRIGGER از نوع AFTER استفاده شود در غیر این صورت نمی توان از آن جدول QUERY گرفت.

 

2.ساخت TRIGGER برای دستورات DDL

برای دستورات DDL نیز می توان TRIGGER ساخت. در ادامه سینتکس ساخت TRIGGER برای دستورات DDL را می بینید  که در سطح دیتابیس یا SCHEMA اعمال می شوند.

 CREATE [OR REPLACE] TRIGGER trigger name

   {BEFORE | AFTER } { DDL event} ON {DATABASE | SCHEMA}

   [WHEN (…)]

   DECLARE

    Variable declarations

   BEGIN

   …some code

   END;

 

مثال: در سطح SCHEMA اطلاعات هر دستور CREATE را ثبت کنید

CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE  
ON SCHEMA
DECLARE
 oper ddl_log.operation%TYPE;
BEGIN
  INSERT INTO ddl_log
  SELECT ora_sysevent, ora_dict_obj_owner,
  ora_dict_obj_name, NULL, USER, SYSDATE
  FROM DUAL;
END bcs_trigger;
/

3.ساخت TRIGGER برای رخدادهای خاص

در ادامه سینتکس ساخت TRIGGER برای رخدادهای خاص در سطح دیتابیس یا SCHEMA را ملاحظه می کنید:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER{ {database_event} ON {DATABASE | SCHEMA}

BEGIN

    PL/SQL Code

END;

/

مثال: عمل LOGON کاربران به دیتابیس را ثبت کنید.

CREATE OR REPLACE TRIGGER   all_lgon_audit

AFTER LOGON ON DATABASE

BEGIN

  INSERT INTO tbl_evnt_audit VALUES(

    ora_sysevent,

    sysdate,

    TO_CHAR(sysdate, ‘hh24:mi:ss’),

    USER,

    NULL

  );

  COMMIT;

END;

/

 

حذف TRIGGER

برای حذف کردن یک TRIGGER از دستور DROP استفاده می شود:

DROP TRIGGER trigger_name;

 

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

برای غیرفعال کردن TRIGGER از دستور زیر استفاده می شود:

ALTER TRIGGER orders_before_insert DISABLE;

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

ALTER TABLE table_name DISABLE ALL TRIGGERS;

تمام TRIGGERهای یک جدول به این روش فعال می شوند:

ALTER TABLE table_name ENABLE ALL TRIGGERS;

 

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

نشانی ایمیل شما منتشر نخواهد شد.