در این قسمت 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;
