DML = Data Manipulation Language: select, insert, update, delete data.
2.1 The COMPANY database (used in all examples)
EMPLOYEE (SSN, FNAME, MINIT, LNAME, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO)
DEPARTMENT (DNUMBER, DNAME, MGRSSN, MGRSTARTDATE)
PROJECT (PNUMBER, PNAME, PLOCATION, DNUM)
DEPENDENT (ESSN, DEPENDENT_NAME, SEX, BDATE, RELATIONSHIP)
DEPT_LOCATIONS (DNUMBER, DLOCATION)
WORKS_ON (ESSN, PNO, HOURS)2.2 The SELECT statement
- SQL has one basic retrieval statement:
SELECT. - SQL
SELECT≠ algebra SELECT (σ). SQL SELECT clause ≈ π, WHERE ≈ σ. - SQL tables are multisets (duplicates allowed); use
DISTINCTto get sets.
SELECT <attribute list> -- attributes to retrieve
FROM <table list> -- relations needed
WHERE <condition> -- Boolean expression identifying tuples2.3 Relational algebra ↔ SQL
| Algebra | SQL |
|---|---|
| π list | SELECT DISTINCT list |
| σ cond | WHERE cond |
| R × S | FROM R, S (no join condition) |
| R ⋈c S | FROM R, S WHERE c or R JOIN S ON c |
| R ⋈ S (natural) | R NATURAL JOIN S |
| ⟕ ⟖ ⟗ | LEFT / RIGHT / FULL OUTER JOIN |
| ∪ ∩ − | UNION, INTERSECT, MINUS/EXCEPT |
| ← | aliases, AS, views |
| ÷ | NOT EXISTS (… NOT EXISTS …) |
2.4 Simple queries
Q0 — one relation (select‑project)
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME='John' AND MINIT='B' AND LNAME='Smith';Result may contain duplicate tuples.
Q1 — select‑project‑join
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' -- selection condition (σ)
AND DNUMBER=DNO; -- join condition (⋈)Q2 — two join conditions
SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER -- project → controlling department
AND MGRSSN=SSN -- department → its manager
AND PLOCATION='Stafford';2.5 Qualified names & aliases
- Same attribute name in two relations → qualify it:
RELATION.ATTRIBUTE. - Referring to the same relation twice → give aliases (tuple variables).
-- Q3: each employee and his/her immediate supervisor
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE E, EMPLOYEE S -- E = supervisee, S = supervisor
WHERE E.SUPERSSN = S.SSN;(EMPLOYEE AS E is equivalent.)
2.6 Missing WHERE & *
SELECT SSN FROM EMPLOYEE; -- Q4: all tuples
SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT; -- Q5: CARTESIAN PRODUCT!
SELECT * FROM EMPLOYEE WHERE DNO=5; -- Q6: * = all attributes
SELECT * FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNO=DNUMBER; -- Q72.7 DISTINCT
SELECT SALARY FROM EMPLOYEE; -- Q8: may contain duplicates
SELECT DISTINCT SALARY FROM EMPLOYEE; -- Q8A: no duplicates2.8 Set operations
UNION; in some SQL versions alsoMINUS(difference) andINTERSECT.- Only on union‑compatible relations; duplicates eliminated.
-- Q9: projects involving an employee named Smith as worker OR as manager
(SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith')
UNION
(SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith');2.9 Nested queries
- A full SELECT (inner/nested query) inside the WHERE of an outer query.
v IN V→ TRUE if v is an element of the set/multiset V.
-- Q1B
SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research');
-- Q10: correlated nested query (inner refers to outer E)
SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E
WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT
WHERE DEPENDENT_NAME = E.FNAME);
-- Q10A: same query as a join
SELECT E.FNAME, E.LNAME FROM EMPLOYEE E, DEPENDENT D
WHERE E.SSN=D.ESSN AND E.FNAME=D.DEPENDENT_NAME;
-- Q11: explicit (enumerated) set of values
SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3);2.10 ANY / SOME / ALL
v <op> ANY (V)(=SOME) — TRUE if the comparison holds for at least one value of V.op ∈ {=, <, ≤, >, ≥, ≠}. (= ANY≡IN)v <op> ALL (V)— TRUE if the comparison holds for every value of V.
-- someone in dept 2 earning more than at least one employee of dept 5
SELECT Lname, Fname FROM Employee
WHERE SALARY > ANY (SELECT Salary FROM Employee WHERE DNO=5) AND DNO=2;
-- employees of dept 2 earning more than the HIGHEST salary of dept 5
SELECT Lname, Fname FROM Employee
WHERE SALARY > ALL (SELECT Salary FROM Employee WHERE DNO=5) AND DNO=2;2.11 EXISTS / NOT EXISTS
Checks whether the result of a (usually correlated) nested query is empty or not.
-- Q10B
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE EXISTS (SELECT * FROM DEPENDENT
WHERE ESSN=SSN AND DEPENDENT_NAME=FNAME);
-- Q12: employees with no dependents
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE ESSN=SSN);For each EMPLOYEE tuple the inner query is re‑evaluated; if no DEPENDENT tuple exists, the employee is selected.
2.12 NULL & ranges
- Compare NULLs with
IS/IS NOT, never=: each NULL is considered distinct from every other NULL.
SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL; -- Q13
SELECT Lname, Fname FROM Employee
WHERE Bdate BETWEEN '1990-01-01' AND '1992-12-12'; -- inclusive range2.13 Joined relations (join in the FROM clause)
Types: JOIN (inner), NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.
-- Q14: all employees + their supervisor if they have one
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN=S.SSN);
-- Q1C (= Q1)
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO)
WHERE DNAME='Research';
-- Q2A: multiple joins nested
SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM ((PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER)
JOIN EMPLOYEE ON MGRSSN=SSN)
WHERE PLOCATION='Stafford';2.14 Aggregate functions
COUNT, SUM, MAX, MIN, AVG
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE; -- Q15
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research'; -- Q16
SELECT COUNT(*) FROM EMPLOYEE; -- Q17
SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME='Research'; -- Q182.15 GROUP BY
Applies aggregates to subgroups of tuples — each group = tuples with the same value of the grouping attribute(s).
-- Q19: per department: number, #employees, average salary
SELECT DNO, COUNT(*), AVG(SALARY) FROM EMPLOYEE GROUP BY DNO;
-- Q20: per project: number, name, #employees
SELECT PNUMBER, PNAME, COUNT(*)
FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME;Rule: every non‑aggregated attribute in SELECT must appear in GROUP BY.
2.16 HAVING
A condition on groups (WHERE filters tuples, before grouping; HAVING filters groups, after).
-- Q21: projects with more than two employees
SELECT PNUMBER, PNAME, COUNT(*)
FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT(*) > 2;2.17 Substring comparison — LIKE
%(or*in some implementations) = any number of characters._= exactly one character.
SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Jounieh%'; -- Q22
SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE '__5_______'; -- Q23: born in the 1950s(Q23 assumes a date format where ‘5’ is the 3rd character.)
2.18 Arithmetic
+ - * / on numeric values in the result.
-- Q24: effect of a 10% raise for ProductX workers (no data changed)
SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX';2.19 ORDER BY
-- Q25: by department, then alphabetically by last name
SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER
ORDER BY DNAME, LNAME;DESC = descending; ASC = ascending (default, may be written explicitly), e.g. ORDER BY DNAME DESC, LNAME ASC.
2.20 General form of SELECT
Up to six clauses, only SELECT and FROM mandatory, in this order:
SELECT <attribute list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>]Logical evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
2.21 INSERT
- Values listed in the same order as in CREATE TABLE.
-- U1
INSERT INTO EMPLOYEE
VALUES ('653298653','Tony','K','Amar','30-12-1960',
'98 Jounieh-Kaslik-Lebanon','M',37000,'987654321',4);
-- U1A: explicit attribute names; omitted attributes get NULL (or default)
INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES ('Tony','Amar','653298653');- Multiple tuples from a query:
-- U2A: temporary summary table
CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(10),
NO_OF_EMPS INTEGER,
TOTAL_SAL INTEGER);
-- U2B: load it
INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)
SELECT DNAME, COUNT(*), SUM(SALARY)
FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO
GROUP BY DNAME;2.22 DELETE
- Removes tuples selected by WHERE; from one table at a time (unless CASCADE is set on a referential integrity constraint).
- No WHERE → all tuples deleted (table becomes empty, but still exists).
DELETE FROM EMPLOYEE WHERE LNAME='Brown'; -- U3A
DELETE FROM EMPLOYEE WHERE SSN='123456789'; -- U3B
DELETE FROM EMPLOYEE WHERE DNO IN
(SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research'); -- U3C
DELETE FROM EMPLOYEE; -- U3D: empties the table2.23 UPDATE
WHERE selects the tuples; SET gives attributes and their new values.
UPDATE PROJECT SET PLOCATION='Bellaire', DNUM=5 WHERE PNUMBER=10; -- U4
UPDATE EMPLOYEE SET SALARY = SALARY*1.1
WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research'); -- U52.24 Views
- A virtual table derived from other tables (not stored as data).
CREATE VIEWneeds: a name, an optional list of attribute names (needed when using arithmetic/aggregates or to rename), and a query defining the contents.
CREATE VIEW WORKS_ON_NEW AS
SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO=PNUMBER;
SELECT FNAME, LNAME FROM WORKS_ON_NEW WHERE PNAME='Research';
DROP VIEW WORKS_ON_NEW; -- slide writes "DROP WORKS_ON_NEW"; standard syntax is DROP VIEWPart 3 — Procedural DML (PL/SQL)
3.1 Cursors
- To run a query, PL/SQL opens a work area; a cursor gives access to it.
- Cursor = data structure storing the result of a query that returns multiple rows.
- Two types:
- Explicit — declared by the user.
- Implicit — created automatically by Oracle (named
SQL) for each DML /SELECT … INTO.
Explicit cursor life cycle
| Step | Where | Syntax | Effect |
|---|---|---|---|
| Declare | DECLARE section | CURSOR c IS select_query; | defines the query |
| Open | BEGIN section | OPEN c [(args)]; | executes the query, positions on 1st row |
| Fetch | body (inside a loop) | FETCH c INTO var_list; or INTO record; | reads one row, moves to next |
| Close | body | CLOSE c; | frees memory |
FETCH … INTO: one variable per returned column, or a recordrec table%ROWTYPE.- To traverse all rows →
FETCHinside aLOOPwithEXIT WHEN c%NOTFOUND.
DECLARE
CURSOR empCur IS SELECT * FROM Employee;
emp_record Employee%ROWTYPE;
BEGIN
OPEN empCur;
FETCH empCur INTO emp_record;
CLOSE empCur;
END;Cursor attributes
| Attribute | Implicit SQL%… | Explicit cursor_name%… |
|---|---|---|
%FOUND | INSERT/UPDATE/DELETE affected ≥ 1 row; SELECT … INTO returned exactly 1 row | last FETCH returned a row |
%NOTFOUND | INSERT/UPDATE/DELETE/SELECT…INTO affected 0 rows | last FETCH returned no row |
%ISOPEN | always FALSE (closed automatically after use) | TRUE if the cursor is open |
%ROWCOUNT | INSERT/UPDATE/DELETE: number of rows affected; SELECT…INTO: 0, 1 (or 2 if > 1 row → error) | number of rows fetched so far (n‑th row) |
Complete example — 10% raise for salaries < 1500
DECLARE
CURSOR SalCur IS SELECT * FROM Employee WHERE salary < 1500.00;
emp Employee%ROWTYPE;
BEGIN
OPEN SalCur;
LOOP
FETCH SalCur INTO emp;
EXIT WHEN SalCur%NOTFOUND;
UPDATE Employee SET salary = salary*1.1 WHERE EmpNo = emp.empno;
END LOOP;
CLOSE SalCur;
END;Exercise — sum & average of salaries for ages 20–40
DECLARE
CURSOR cur IS SELECT salary FROM Employee WHERE age >= 20 AND age <= 40;
salaryEmployee Employee.salary%TYPE; -- %TYPE = same type as column
sumSalaries NUMBER(10,2) := 0;
avgSalaries NUMBER(10,2);
BEGIN
OPEN cur;
LOOP
FETCH cur INTO salaryEmployee;
EXIT WHEN cur%NOTFOUND;
sumSalaries := sumSalaries + salaryEmployee;
END LOOP;
avgSalaries := sumSalaries / cur%ROWCOUNT; -- ⚠ slide 13 divides avgSalaries (bug); use sumSalaries
CLOSE cur; -- read %ROWCOUNT BEFORE closing
DBMS_OUTPUT.PUT_LINE('Average: ' || avgSalaries || ' Sum: ' || sumSalaries);
END;DBMS_OUTPUT.PUT_LINE prints; || concatenates. (Guard against cur%ROWCOUNT = 0.)
Parametric cursor
- One definition reused with different values instead of several cursors.
CURSOR cursor_name(param1 [IN] type [{:= | DEFAULT} value], …) IS select_query;
OPEN cursor_name(value1, value2, …); -- values passed at OPEN- Parameter names are unknown outside the cursor definition.
- Can be reopened with new values, but must be closed first.
CURSOR cur(ageInf NUMBER, ageSup NUMBER) IS
SELECT salary FROM Employee WHERE age >= ageInf AND age <= ageSup;
…
OPEN cur(20, 40);(⚠ the slide prints ageInf/ageSup in PUT_LINE — not allowed, since they’re unknown outside the cursor; print literals or use variables.)
FOR loop cursor
- Implicit
OPEN,FETCHat each iteration, andCLOSE. - Loop variable is implicitly declared as
%ROWTYPEof the cursor.
DECLARE
CURSOR cur IS SELECT fname, lname FROM Employee WHERE dept = 10;
BEGIN
FOR empl IN cur LOOP
DBMS_OUTPUT.PUT_LINE('Last name: ' || empl.lname || ', First name: ' || empl.fname);
END LOOP;
END;3.2 Stored procedures & functions
CREATE [OR REPLACE] PROCEDURE name
[(p1 [IN | OUT | IN OUT] [NOCOPY] type1 [{:= | DEFAULT} expr], …)]
{IS | AS} [declarations;]
BEGIN instructions; END [name];
CREATE [OR REPLACE] FUNCTION name
[(p1 [IN | OUT | IN OUT] [NOCOPY] type1 [{:= | DEFAULT} expr], …)]
RETURN return_type
{IS | AS} [declarations;]
BEGIN instructions; RETURN value; END [name];
DROP {PROCEDURE | FUNCTION} name;OR REPLACE= create or modify.
Parameter modes
| Mode | Inside subprogram | Actual argument | Passing |
|---|---|---|---|
IN (default) | read‑only, cannot be assigned | constant, variable or expression | by reference |
OUT | written | must be a variable | by value (copy) |
IN OUT | read & written | must be a variable | by value (copy) |
NOCOPY (hint) | — | — | forces reference for OUT/IN OUT — useful for large values |
- Functions should use only IN parameters (OUT/IN OUT cause problems when called from SQL).
Calling
| From | Procedure | Function |
|---|---|---|
| SQL*Plus | EXECUTE proc(args); | inside an SQL statement: … func(args) … |
| PL/SQL block | proc(args); | inside a PL/SQL or SQL statement: x := func(args); |
Examples
CREATE OR REPLACE PROCEDURE giveBonus(
cityBonus IN Employee.city%TYPE,
valBonus IN NUMBER, -- slide: NUMBER(5,2); PL/SQL params can't have size constraints
nbEmployees OUT INTEGER) IS
BEGIN
UPDATE Employee SET salary = salary + valBonus WHERE city = cityBonus;
nbEmployees := SQL%ROWCOUNT; -- implicit cursor attribute
END giveBonus;
-- call from PL/SQL
DECLARE nbEmpBonus INTEGER := 0;
BEGIN
giveBonus('Lyon', 100, nbEmpBonus);
DBMS_OUTPUT.PUT_LINE('Employees with bonus: ' || nbEmpBonus);
END;CREATE OR REPLACE FUNCTION salaryAverage(
ageInf IN employee.Age%TYPE, ageSup IN employee.Age%TYPE)
RETURN employee.salary%TYPE IS
salaryAvg employee.salary%TYPE;
BEGIN
SELECT AVG(salary) INTO salaryAvg FROM employee
WHERE Age >= ageInf AND Age <= ageSup;
RETURN salaryAvg;
END salaryAverage;
SELECT * FROM employee WHERE salary <= salaryAverage(20,40); -- from SQLSELECT … INTO var = single‑row query stored in a variable.
Hands‑on schema
Course(CourseID, CourseName, nb_Credits, #coreq_Course)
Section_Course(SecID, section, semester, #c_course, #ProfID)
Professor(ProfID, Fname, Lname, dateNaiss, rank, salary)
Student(StdID, Fname, Lname, address, dateofB)
Enrollment(#StdID, #SecID, grade)
Ex 1 — constant grade increase for a course’s sections in a semester
CREATE OR REPLACE PROCEDURE RaiseGrade(
code_Course IN Section_Course.c_course%TYPE,
sem IN Section_Course.semester%TYPE,
raise IN NUMBER,
nbstudents OUT INTEGER) IS
BEGIN
UPDATE Enrollment SET grade = grade + raise
WHERE SecID IN (SELECT SecID FROM Section_Course
WHERE c_course = code_Course AND semester = sem);
nbstudents := SQL%ROWCOUNT;
END RaiseGrade;Ex 2 — average of a student’s grades
CREATE OR REPLACE FUNCTION AVGgrades(ID IN Student.StdID%TYPE)
RETURN Enrollment.grade%TYPE IS
av Enrollment.grade%TYPE;
BEGIN
SELECT AVG(grade) INTO av FROM Enrollment WHERE StdID = ID;
RETURN av;
END AVGgrades;Ex 3 — cap at 100 (no solution on slides — proposed)
UPDATE Enrollment SET grade = LEAST(grade + raise, 100)
WHERE SecID IN (…same subquery…);Ex 4 — repeated course counts only its highest grade (proposed)
SELECT AVG(best) INTO av
FROM (SELECT MAX(e.grade) AS best
FROM Enrollment e JOIN Section_Course s ON e.SecID = s.SecID
WHERE e.StdID = ID
GROUP BY s.c_course);3.3 Triggers
- A program automatically executed after/before an event; it cannot be called explicitly (unlike a procedure).
- Event = DML statement modifying the DB:
INSERT,UPDATE,DELETE. - Part of the DB schema, compiled code stored; part of SQL3.
- Uses: validate input data, audit the database, derive additional data, maintain complex integrity rules.
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} event [OF column [, column]…] ON table_name
[FOR EACH ROW]
[WHEN (condition)]
<PL/SQL block>
-- event = INSERT | UPDATE | DELETE | any combination (INSERT OR DELETE …)OF column restricts an UPDATE trigger to specific columns.
Statement‑level vs row‑level
Statement trigger (no FOR EACH ROW) | Row trigger (FOR EACH ROW) | |
|---|---|---|
| Fires | once per event | once per affected row (even for a group update like SET salary=salary*1.5) |
| Row values | no access | access :old / :new |
| Suited for | finding the author / date of the event | business rules & security |
BEFORE / AFTER
- BEFORE: runs before the event; a BEFORE row trigger can modify the values being inserted/updated (
:new). - AFTER: runs after the event; an AFTER row trigger cannot modify the row values.
:old and :new
| Event | :old | :new |
|---|---|---|
| INSERT | — | new values |
| UPDATE | old values | new values |
| DELETE | old values | — |
(In the WHEN clause they are written without the colon: new.qty.) |
Managing triggers
ALTER TRIGGER name DISABLE; ALTER TABLE t DISABLE ALL TRIGGERS;
ALTER TRIGGER name ENABLE; ALTER TABLE t ENABLE ALL TRIGGERS;
DROP TRIGGER name;Example — automatic reorder
CREATE OR REPLACE TRIGGER maintainStock
AFTER INSERT OR UPDATE ON Stock
FOR EACH ROW
WHEN (new.quantiteAvailable < 10 OR new.quantiteAvailable IS NULL)
BEGIN
INSERT INTO Orders (productID, quantity) VALUES (:new.ProductID, 200);
END;raise_application_error(negative_int, message)
- Procedure of package DBMS_STANDARD: raises an exception, returns the message to the environment and rolls back the transaction.
- Error number between ‑20000 and ‑20999 (slide says ‑20500).
- Called in a trigger → cancels the triggering statement.
Exercise 1 — check stock on sale
Sales(ID, productID, quantity, price), Stock(productID, quantity)
CREATE OR REPLACE TRIGGER tgSale
BEFORE INSERT ON Sales -- BEFORE so the sale can be blocked
FOR EACH ROW
DECLARE
qt Stock.quantity%TYPE;
BEGIN
SELECT quantity INTO qt FROM Stock WHERE productID = :new.productID;
IF :new.quantity > qt THEN
raise_application_error(-20000, 'Insufficient stock');
ELSE
UPDATE Stock SET quantity = quantity - :new.quantity
WHERE productID = :new.productID;
END IF;
END;(Slide typos fixed: missing ;, SET quantity := → =.)
Exercise 2 — credit‑card fraud detection
Fraud probability > 80% when cumulated credit > 5000 $ over the last 3 days → log the account.
Credits(transactionID, accountID, transactionDate, amount, cardID), ThreatedAccounts(accountID, cardID, amount, transactionID)
CREATE OR REPLACE TRIGGER FraudDetection
AFTER INSERT ON Credits
FOR EACH ROW
DECLARE
last3daysAmount NUMBER(8,2);
BEGIN
SELECT SUM(amount) INTO last3daysAmount FROM Credits -- slide says "Debits"
WHERE cardID = :new.cardID AND transactionDate >= SYSDATE - 3;
IF last3daysAmount > 5000 THEN
INSERT INTO ThreatedAccounts (accountID, cardID, amount, transactionID)
VALUES (:new.accountID, :new.cardID, last3daysAmount, :new.transactionID);
END IF;
END;⚠ In Oracle, a row trigger reading the same table it fires on raises a mutating table error (ORA‑04091). Exam answer is as above; in practice use a compound/statement trigger.