ìm hiểu Trigger trong Oracle - Oracle nâng cao
Khi học bất kì một hệ quản trị CSDL nào bạn cũng phải tìm hiểu qua trigger, bởi đây là tính năng giúp bạn bảo vệ tính toàn vẹn dữ liệu, DBA sẽ kiểm soát được những thao tác từ lập trình viên code ứng dụng. Oracle cũng là một hệ QT CSDL nên không ngoại lệ. Với những dự án lớn thì DBA sẽ cung cấp ...
Khi học bất kì một hệ quản trị CSDL nào bạn cũng phải tìm hiểu qua trigger, bởi đây là tính năng giúp bạn bảo vệ tính toàn vẹn dữ liệu, DBA sẽ kiểm soát được những thao tác từ lập trình viên code ứng dụng. Oracle cũng là một hệ QT CSDL nên không ngoại lệ.
Với những dự án lớn thì DBA sẽ cung cấp nhưng procedure, function cho lập trình viên, kèm theo đó là dữ liệu đầu vào và dữ liệu đầu ra. Như vậy lập trình viên sẽ không quan tâm đến nội dung bên câu SQL có gì, họ chỉ cần biết INPUT và OUTPUT.
1. Trigger trong Oracle là gì?
Dịch ra tiếng Anh thì Trigger có nghĩa là cò súng, ý muốn nói rằng nó sẽ được kích hoạt khi có một thao tác nào đó lên cò súng này.
Trong hệ quản trị CSDL thì chúng ta có ba thao tác làm thay đổi dữ liệu chính đó là: UPDATE, INSERT và DELETE. Và đôi lúc bạn muốn thực hiện một hành động gì đó trước khi hoặc sau khi 3 lệnh đó xảy ra, và cái này ta gọi là trigger.
Như vậy chúng ta có 6 câu lệnh để tạo trigger chính, hay nói cách khác là 6 hành động và gom thành hai nhóm.
Nhóm before:
- BEFORE INSERT TRIGGER
- BEFORE UPDATE TRIGGER
- BEFORE DELETE TRIGGER
Nhóm after:
- AFTER INSERT TRIGGER
- AFTER UPDATE TRIGGER
- AFTER DELETE TRIGGER
Lưu ý:
- Bạn không thể tạo Before Trigger cho View
- Bạn không thể cập nhật dữ liệu cũ, chỉ co thể cập nhật dữ liệu mới.
Trước khi tìm hiểu trigger mình muốn tạo một bảng để thực hành như sau:
CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2) );
Bảng này sẽ được dùng xuyên suốt bài học này.
2. After Trigger trong Oracle
After trigger là những trigger sẽ được thực thi sau khi hành động chính hoàn thành.
CREATE [ OR REPLACE ] TRIGGER trigger_name AFTER INSERT OR UPDATE OR DELETE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;
Sau đây là một vài ví dụ về trigger trong Oracle.
CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by)
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
END;CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the INSERT into the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
username )
VALUES
( :new.order_id,
:new.quantity,
:new.cost_per_item,
:new.total_cost,
v_username );
END;CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
END;3. Before Trigger trong Oracle
Before Trigger là những trigger sẽ thực thi trước hành động chính, nghĩa là xử lý nó xong thì hành động chính mới được thực hiện.
CREATE [ OR REPLACE ] TRIGGER trigger_name BEFORE DELETE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;
Sau đây là một vài ví dụ:
CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by )
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
END;CREATE [ OR REPLACE ] TRIGGER trigger_name BEFORE INSERT ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;
CREATE OR REPLACE TRIGGER orders_before_update BEFORE UPDATE ON orders FOR EACH ROW DECLARE v_username varchar2(10); BEGIN -- Find username of person performing UPDATE on the table SELECT user INTO v_username FROM dual; -- Update updated_date field to current system date :new.updated_date := sysdate; -- Update updated_by field to the username of the person performing the UPDATE :new.updated_by := v_username; END;
4. Disable trigger cho table
Nếu bạn muốn tắt một trigger đang hoạt động ở một table nào đó thì dùng cú pháp sau:
ALTER TRIGGER trigger_name DISABLE;
Nếu bạn muốn tắt tất cả trigger đang hoạt động ở một table nào đó thì dùng cú pháp sau:
ALTER TABLE table_name DISABLE ALL TRIGGERS;
Trong đó table_name là tên table muốn xóa trigger.
5. Delete Trigger
Nếu bạn muốn xóa trigger vĩnh viễn thì sử dụng lệnh drop trigger.
DROP TRIGGER trigger_name;
6. Enable trigger
Nếu bạn muốn bật một trigger đã tắt cho table thì dùng cú pháp sau:
ALTER TRIGGER trigger_name ENABLE;
Nếu bạn muốn bật tất cả trigger đã tắt cho table thì dùng cú pháp sau:
ALTER TABLE table_name ENABLE ALL TRIGGERS;
7. Lời kết
Như vậy mình đã giới thiệu tất cả các chức năng mà Oracle hỗ trợ khi làm việc với trigger. Thực sự mà nói thì trigger đóng vai trò không thể thiếu, nó giúp cho DBA đảm bảo rằng dữ liệu luôn luôn được bảo toàn, tránh trường hợp không đồng nhất dữ liệu.
