1) BEFORE ROW trigger on EMP — calculate commission for new employees in
department 30
This trigger runs BEFORE INSERT for each row. If the new row belongs to
department 30, it assigns a default commission percentage (10% = 0.10) only if
:NEW.commission_pct is NULL. Change the percentage or logic as needed.
-- Trigger: compute commission_pct for new employees in dept 30
CREATE OR REPLACE TRIGGER trg_emp_commission
BEFORE INSERT ON emp -- or on employees if your table name is employees
FOR EACH ROW
BEGIN
-- Only assign commission for department 30, and only if not already provided
IF :NEW.department_id = 30 THEN
IF :NEW.commission_pct IS NULL THEN
-- Assumption: commission_pct stores fraction (0.10 for 10%)
:NEW.commission_pct := 0.10; -- set default 10% commission
END IF;
END IF;
END trg_emp_commission;
Test example (insert into emp)
INSERT INTO emp (employee_id, first_name, last_name, email, hire_date, job_id,
salary, department_id)
VALUES (9999, 'Test', 'User', '[Link]', SYSDATE, 'SA_REP', 5000, 30);
SELECT employee_id, first_name, salary, commission_pct, department_id FROM
emp WHERE employee_id = 9999;
2) ROW-LEVEL trigger on customers — display salary di erence on INSERT /
UPDATE / DELETE
This is a FOR EACH ROW trigger that fires on INSERT OR UPDATE OR DELETE. It
prints appropriate messages via DBMS_OUTPUT.PUT_LINE. For UPDATE it prints
new_salary - old_salary. For INSERT and DELETE it reports the new or old salary
respectively.
If your customers table uses a di erent column name for salary (e.g., SAL), replace
salary with your column name.
-- Row-level trigger to display salary di erence/info on DML
CREATE OR REPLACE TRIGGER trg_customers_salary_di
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW
DECLARE
v_di NUMBER;
BEGIN
IF INSERTING THEN
-- On insert, show the newly inserted salary
DBMS_OUTPUT.PUT_LINE('CUSTOMER INSERTED: New Salary = ' ||
NVL(TO_CHAR(:[Link]),'NULL'));
ELSIF DELETING THEN
-- On delete, show the old salary being removed
DBMS_OUTPUT.PUT_LINE('CUSTOMER DELETED: Old Salary = ' ||
NVL(TO_CHAR(:[Link]),'NULL'));
ELSIF UPDATING THEN
-- On update, show di erence (new - old)
v_di := NVL(:[Link],0) - NVL(:[Link],0);
DBMS_OUTPUT.PUT_LINE('CUSTOMER UPDATED: Old Salary = ' ||
NVL(TO_CHAR(:[Link]),'NULL') ||
', New Salary = ' || NVL(TO_CHAR(:[Link]),'NULL') ||
', Di erence (New-Old) = ' || TO_CHAR(v_di ));
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in trg_customers_salary_di : ' || SQLERRM);
RAISE;
END trg_customers_salary_di ;
Test examples
-- INSERT test
INSERT INTO customers(customer_id, name, salary) VALUES (9001, 'Alice', 45000);
-- UPDATE test
UPDATE customers SET salary = 48000 WHERE customer_id = 9001;
-- DELETE test
DELETE FROM customers WHERE customer_id = 9001;
(Each action will emit DBMS_OUTPUT lines showing the salary/new-old di erence.)
3) STATEMENT-LEVEL trigger on customers — insert count value into table R
First create the target table R. I name the column cnt (you said R has only one field
count; COUNT is a reserved word so I avoid using it as identifier — if you insist on
COUNT, quote or rename).
-- Create table R to store counts
CREATE TABLE R (
cnt NUMBER
);
Now the statement-level trigger. It fires AFTER INSERT OR UPDATE OR DELETE on
customers. It inserts into R the current total number of rows in customers (i.e.,
SELECT COUNT(*) FROM customers) — which gives a stable and meaningful count
after the statement completes. If instead you want the number of rows a ected by
the DML (changed rows), I also show an alternate commented option below.
-- Statement-level trigger to insert count of customers into table R
CREATE OR REPLACE TRIGGER trg_customers_count_to_R
AFTER INSERT OR UPDATE OR DELETE ON customers
DECLARE
v_total NUMBER;
BEGIN
-- Option A (chosen): store total number of rows currently in customers
SELECT COUNT(*) INTO v_total FROM customers;
INSERT INTO R (cnt) VALUES (v_total);
COMMIT;
/*
-- Option B (alternate): store number of rows a ected by the triggering statement.
-- Note: SQL%ROWCOUNT inside AFTER statement trigger returns the #rows
processed by the triggering statement.
-- To use Option B, replace the above block with:
v_total := SQL%ROWCOUNT;
INSERT INTO R (cnt) VALUES (v_total);
COMMIT;
*/
EXCEPTION
WHEN OTHERS THEN
-- avoid breaking the triggering DML; log error for debugging
DBMS_OUTPUT.PUT_LINE('Error in trg_customers_count_to_R: ' || SQLERRM);
ROLLBACK;
RAISE;
END trg_customers_count_to_R;