mysql trigger is not enforcing before insert trigger

user_4083786 0
Viewed 28

i am trying to enforce unique constraints. i have two tables, with a common column puuid. The value is unique in both. If a user tries inserting a puuid into table1 and it already exists in table2, it should prevent me from doing so. i am not able to get it to work. heres my query:

create table if not exists table1
(puuid varchar(50) not null unique)

create table if not exists table2
(puuid varchar(50) not null unique)

delimiter $$
create trigger temp_trigger
before INSERT
on table2 t2 for each ROW 
BEGIN 
    declare c int,
    select count(*) into c from table1 t1 where t1.puuid = NEW.puuid
    if (c > 0) THEN 
    -- abort insert because puuid cannot be null
    set NEW.puuid = NULL;
    end if;
    
END$$

delimiter;

insert into table1 (puuid)
values ('22')

insert into table2 (puuid)
values ('22')

Its still successfully inserting. what am i doing wrong?

mysql
1 Answers

your code had some errors, corrected the code make what you need

but a better way is to used SIGNAL Statement

see the second trigger

create table if not exists table1
(puuid varchar(50) not null unique);

create table if not exists table2
(puuid varchar(50) not null unique);
create table if not exists table3
(puuid varchar(50) not null unique)

create trigger temp_trigger
before INSERT
on table2  for each ROW 
BEGIN 
    declare c int;
    select count(*) into c from table1 t1 where t1.puuid = NEW.puuid;
    if (c > 0) THEN 
    -- abort insert because puuid cannot be null
    set NEW.puuid = NULL;
    end if;
    
END;

insert into table1 (puuid)
values ('22');

insert into table2 (puuid)
values ('22')

Column 'puuid' cannot be null

create trigger temp_trigger2
before INSERT
on table3  for each ROW 
BEGIN 
    declare c int;
    select count(*) into c from table1 t1 where t1.puuid = NEW.puuid;
    if (c > 0) THEN 
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'id already exists';
    end if;
    
END;

insert into table3 (puuid)
values ('22')

id already exists

SELECT * FROM table1;

| puuid |
| :---- |
| 22    |

SELECT * FROM table2;

| puuid |
| :---- |

SELECT * FROM table3;

| puuid |
| :---- |

db<>fiddle here

user_51935360
Related