Creating your first PostgreSQL trigger

--Membuat table santri
create table santri
(
    id serial primary key,
    first_name varchar (50) not null,
    last_name varchar (50) not null,
    address_santri varchar (50) not null
)

--Membuat table audit_santri
create table audit_santri
(
    id serial primary key,
    id_santri int not null,
    first_name varchar(50) not null,
    last_name varchar(50) null,
    change_on timestamp(6) not null
)

--Memasukkan data table santri
insert into santri (first_name, last_name, address_santri) values
('Rahmad', 'Nasution', 'Medan'),
('Wandi', 'Pratama', 'Prabumuli'),
('Muhammad', 'Andari', 'Sukabumi'),
('Rahmadi', 'Cahaya Putra', 'Jambi'),
('Wildan', 'Wildani', 'Bekasi'),
('Arif', 'Lutfi', 'Lampung'),
('Hafif', 'Albusyro', 'Jakarta'),
('Rafi', 'Bahru Risky', 'Jakarta');

--Membuat function trigger log_first_name
create or replace function log_first_name_change()
    returns trigger as
$BODY$
begin
    if new.first_name <> old.first_name then
        insert into audit_santri(id_santri, first_name, last_name, change_on)
        values (old.id, old.first_name, old.last_name, now());
    end if;
    return new;
end; $BODY$
language plpgsql;

--Membuat trigger first_name_changes
create trigger first_name_changes
    before update
    on santri
    for each row
    execute procedure log_first_name_change();


--MengUpdate first_name_santri
update santri
set first_name = 'Wandi'
where id = 2;

--Membuat function log_last_name_change
create or replace function log_last_name_change()
    returns trigger as
$body$
begin
    if new.last_name <> old.last_name then
        insert into audit_santri(id_santri, first_name,last_name, change_on)
        values(old.id, old.first_name,old.last_name, now());
    end if;
    return new;
end; $body$
language plpgsql;

--Membuat trigger last_name_changes
create trigger last_name_changes
    before update
    on santri
    for each row
    execute procedure log_last_name_change();

--Review table santri
select * from santri


--Review table audit_santri
select * from audit_santri

--MengUpdate last_name_santri
update santri
set last_name = 'Wildani'
where id = 5;