Creating Trigger That Runs on Two Tables

Creating Trigger that runs on two tables

This worked perfectly.

CREATE OR REPLACE TRIGGER checkDuration
  BEFORE INSERT OR UPDATE on offering
  FOR EACH ROW
DECLARE
  isFound NUMBER;
BEGIN
  SELECT 1 INTO isFound FROM DUAL WHERE EXISTS (
    SELECT * FROM Course c
    WHERE c.courseId = :new.courseId AND c.duration = 5);
  IF EXTRACT(MONTH FROM :new.startDate) = 12 
    THEN RAISE_APPLICATION_ERROR(-20001, 'Courses of five days duration cannot be run in December');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;

Creating a Trigger that runs on two tables

Since you only need to perform the check when b_date is in December, it's more efficient to add this as a when condition at the top of the trigger. This also simplifies the trigger logic.

create or replace trigger borrow_check_trg
    before insert on borrow 
    for each row
    when (to_char(new.b_date,'MM') = '12')
declare
    l_loc_id copy.loc_id%type;
begin
    select c.loc_id into l_loc_id
    from   copy c
    where  c.copy_id = :new.copy_id;

    if l_loc_id = 'LC0001' then
       raise_application_error(-20669, 'Books cannot be borrowed from the London store during December'); 
    end if;
end;

SQL - How to create a trigger for two joined tables which is used for inserting

You need to either perform the inserts separately with separate single table insert or merge statements or by using a multi table insert (insert all) statement. Assuming you have a sequence to generate the id you are joining on for example this code will work in a very rudimentary way, but has some significant issues:

create table students ( id number primary key
                      , name varchar2(60));

create table grades( id number not null
                   , value number
                   , constraint grades_fk1 foreign key (id) references students(id));

create sequence student_id_seq;

create or replace view studentgrades as
select name, value from students s join grades g on s.id = g.id;

create or replace trigger studentgrades_ii_trg 
instead of insert on studentgrades
begin
  insert all into students(id, name) values (student_id_seq.nextval, name)
             into grades(id, value) values (student_id_seq.nextval, value)
      select :new.name name, :new.value value from dual;
end;
/

insert into studentgrades values ('Alex',10);
insert into studentgrades values ('Alex',8);

The BIG issue with the above trigger is that every time a grade is inserted for 'Alex' a new student record for 'Alex' is also created instead of reusing the previous student record for 'Alex'. That's probably not the desired behavior. Instead it should probably just insert a new grade record for Alex. One way to acheive this is for the studentgrades view to include the id column from the students table so you can uniquely identify which student to add the grade to, updating the trigger as needed:

create or replace view studentgrades as
select s.id, name, value from students s join grades g on s.id = g.id;

create or replace trigger studentgrades_ii_trg 
instead of insert on studentgrades
declare
  newid students.id%type;
begin
  if :new.id is null then
    newid := student_id_seq.nextval;
  else
    newid := :new.id;
  end if;
  insert all when :new.id is null
             then into students(id, name) values (id, name)
             else into grades(id, value) values (id, value)
      select newid id, :new.name name, :new.value value from dual;
end;
/

insert into studentgrades values (null, 'Paul',10);
insert into studentgrades values (student_id_seq.currval, 'Paul',8);

However, now what happens if you try this:

insert into studentgrades values (student_id_seq.currval, 'Mary',10);

In this case the name is effectively ignored and Paul gets a new grade so again this isn't quite right. The question is should Paul's name be updated to Mary, or should a new student record for Mary be created, or should an exception be raised?

Triggers on multiple tables and update it into single column

CREATE TRIGGER advance_audit 
AFTER UPDATE ON employee 
FOR EACH ROW 
BEGIN
IF NEW.firstname <> OLD.firstname THEN 
    INSERT INTO employees_audit (action, column_name, old_name, new_name, operator)
    VALUES ('update', 'firstname', OLD.firstname, NEW.firstname, SYSTEM_USER());
END IF;
IF NEW.lastname <> OLD.lastname THEN 
    INSERT INTO employees_audit (action, column_name, old_name, new_name, operator)
    VALUES ('update', 'lastname', OLD.lastname, NEW.lastname, SYSTEM_USER());
END IF;
-- the same blocks for each another column to be audited
END

Use AFTER trigger. It will not save into the log if the query fails.

Can we have single trigger for multiple tables in MySQL

Can I have a single trigger for multiple tables in MySQL?

No.

But multiple triggers could invoke the same stored procedure.

Creating trigger to compare dates from two tables

First a BEFORE INSERT trigger will probably result in nothing but issues. see https://sqlite.org/lang_createtrigger.html#cautions_on_the_use_of_before_triggers

So here's a trigger that I believe will work as intended albeit it deleting the inserted row:-

CREATE TRIGGER IF NOT EXISTS afterInsertInPlan 
    AFTER INSERT ON plan 
    WHEN (
            (NOT (new.startdate) BETWEEN 
                (SELECT startdate FROM project WHERE projectID = new.projectID) 
                AND 
                (SELECT enddate FROM project WHERE projectID = new.projectID)
            )
            OR 
            (NOT (new.enddate) BETWEEN 
                (SELECT startdate FROM project WHERE projectID = new.projectID) 
                AND 
                (SELECT enddate FROM project WHERE projectID = new.projectID)
            )
        )
    BEGIN
        DELETE FROM plan WHERE pID = new.pID ;
    END
;

---

Testing/Demo

The above was tested using :-

DROP TABLE IF EXISTS Project;
CREATE TABLE Project (
    projectID varchar(255) NOT NULL UNIQUE,
    name varchar(255) NOT NULL DEFAULT ' ',
    leader varchar(255) NOT NULL DEFAULT ' ',
    budget varchar(255) NOT NULL DEFAULT '0',
    startDate DATE NOT NULL DEFAULT '2000-12-31',
    endDate DATE NOT NULL DEFAULT '2000-12-31'
    CHECK (JulianDay(startDate) <= JulianDay(endDate)),
    PRIMARY KEY (projectID)
);
DROP TABLE IF EXISTS Plan;
CREATE TABLE Plan (
    pID varchar(255) NOT NULL UNIQUE,
    projectID varchar(255) DEFAULT NULL,
    name varchar(255) NOT NULL DEFAULT ' ',
    startDate DATE NOT NULL DEFAULT ' ' ,
    endDate DATE NOT NULL DEFAULT ' ',
    CHECK (endDate >= startDate),
    PRIMARY KEY (pID, projectID),
    FOREIGN KEY (projectID) REFERENCES Project(projectID)
);
DROP TABLE IF EXISTS trigger_log;
CREATE TABLE IF NOT EXISTS trigger_log (id INTEGER PRIMARY KEY, timestamp TEXT DEFAULT CURRENT_TIMESTAMP, trigger_text TEXT);
DROP TRIGGER IF EXISTS beforeInsertInPlan;
CREATE TRIGGER IF NOT EXISTS afterInsertInPlan 
    AFTER INSERT ON plan 
    WHEN (
            (NOT (new.startdate) BETWEEN 
                (SELECT startdate FROM project WHERE projectID = new.projectID) 
                AND 
                (SELECT enddate FROM project WHERE projectID = new.projectID)
            )
            OR 
            (NOT (new.enddate) BETWEEN 
                (SELECT startdate FROM project WHERE projectID = new.projectID) 
                AND 
                (SELECT enddate FROM project WHERE projectID = new.projectID)
            )
        )
    BEGIN
        DELETE FROM plan WHERE pID = new.pID ;
        INSERT INTO trigger_log (trigger_text) VALUES('DELETED FROM Plan Table due to date(s) not within project. pID was '||new.pID);
    END
;

INSERT INTO project  VALUES ('P1','P1','Mary',100,'2021-09-01','2022-09-30');
INSERT INTO plan VALUES ('P1P1','P1','Plan1','2021-09-01','2022-09-30');
INSERT INTO plan VALUES ('P1P2','P1','Plan2','2021-08-01','2022-09-30'); /* X */
INSERT INTO plan VALUES ('P1P3','P1','Plan3','2021-09-01','2022-10-30'); /* X */
INSERT INTO plan VALUES ('P1P4','P1','Plan4','2020-09-01','2022-10-30'); /* X */
INSERT INTO plan VALUES ('P1P5','P1','Plan5','2021-09-01','2021-10-01');
INSERT INTO plan VALUES ('P1P6','P1','Plan6','2021-10-01','2021-11-01');
INSERT INTO plan VALUES ('P1P7','P1','Plan7','2021-11-01','2021-12-01');
SELECT * FROM plan;
/* Cleanup Environment */
SELECT * FROM trigger_log;
DROP TABLE IF EXISTS trigger_log;
DROP TRIGGER IF EXISTS beforeInsertInPlan;
DROP TABLE IF EXISTS Plan;
DROP TABLE IF EXISTS Project;

When run then the results are :-

1. The Plan's in the plan table:-

• i.e. those commented with an X (3) were not inserted

2. The trigger_log (used to confirm triggering when testing) :-

• i.e. the 3 commented with an X that were not inserted have been logged accordingly.

Example of why not to use a BEFORE INSERT trigger

Swapping the trigger to use BEFORE INSERT and :-

All are inserted :-

None are deleted even though logged:-

• i.e. nothing to delete as nothing has been inserted.

SQL: Trigger that references 2 tables

You could do this:

CREATE TRIGGER tr_check_qty ON order_details
FOR INSERT,UPDATE
AS
BEGIN
  -- rollback transaction if any product type in order exceeds stock amount
  IF EXISTS (
    SELECT 
      * 
    FROM 
      inserted
      INNER JOIN products ON inserted.product_id = products.product_id
    GROUP BY
      products.product_id
    HAVING
      SUM(inserted.quantity) > MAX(products.quantity_in_stock)
  )
  BEGIN
    PRINT 'Ordered quantity cannot exceed quantity in stock'
    ROLLBACK TRANSACTION
  END
END

It works for both single-row inserts and multi-row inserts.

---

Related Topics