CODEWITHSUNDEEP
sqltriggersphpmyadminmariadb
Rick
Rick

Reputation: 99

SQL trigger INSERT AFTER

I am trying to setup a trigger in phpMyAdmin. have two tables, one parent, one child. Every time a new user in users is created I want to automaticly create a line in the permissions table with the (AI) generated id from users as user_id. (permissions.user_id is a foreigh key of users.id)

users:

id    email              password
--------------------------------------
1     [email protected]      sdgsdhdfhs
2     [email protected]      dfgsdgdfhg
3     [email protected]      plkdfjvjvv

permissions:

index    user_id     allow_read   allow_write   allow_delete
-------------------------------------------------------------
1        1           1            0             1
2        2           1            1             1
3        3           0            0             0

I've tried (without success):

INSERT
INTO permissions
(user_id)
VALUES
(IDENT_CURRENT('users'))

and

INSERT
INTO permissions
(user_id)
VALUES
(@@IDENTITY)

Upvotes: 0

Views: 1563

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

To access the data of the row that caused a trigger to be executed, you can use NEW and OLD aliases. For INSERT triggers only NEW is available. For DELETE triggers only OLD is available. In UPDATE triggers you can use both. They are used the same way as table aliases (e.g. NEW.id / OLD.id).

Given a parent and a child table as follows:

create table parent_table(
    id int auto_increment primary key,
    pdata varchar(50)
);
create table child_table(
    id int auto_increment primary key,
    parent_id int not null,
    cdata varchar(50) default '',
    foreign key (parent_id) references parent_table(id)
);

To insert a child row when a parent row is inserted:

create trigger insert_parent 
    after insert on parent_table
    for each row
        insert into child_table(parent_id)
        values (new.id);

To delete all related child rows wehen a parent row is deleted:

create trigger delete_parent 
    before delete on parent_table
    for each row
        delete from child_table
        where parent_id = old.id;

Demo: http://rextester.com/EOW74217

However a delete trigger is not necessary if you define your foreign key with ON DELETE CASCADE

foreign key (parent_id) references parent_table(id) on delete cascade

All related child rows will be deleted without a trigger when you delete the parent row.

Demo: http://rextester.com/CWB43482

Upvotes: 2

Related Questions