메모)mysql - Triger에 대한 기초적인 내용

메모)mysql - Triger에 대한 기초적인 내용

Create table indexTBL (first_name varchar(14), last_name varchar(16), hire_date date);

insert into indexTBL

    select first_name, last_name, hire_date

from employees.employees

limit 500;

select * from indexTBL;

select * from indextbl where first_name = 'mary';

create view uv_memberTBL

as

    select membername, memberaddress from membertbl;

select * from uv_membertbl;

select * from membertbl where membername='당탕이';

select * from producttbl where productname = '냉장고';

delimiter //

create procedure myproc()

begin

        select * from memberTBL where memberName = '당탕이';

select * from productTBL where productName = '냉장고';

    end //

delimiter ;

call myproc();

insert into membertbl values ('Figure', '연아', '경기도 군포시 당정동');

select * from memberTBL;

update membertbl set memberaddress = '서울 강남구 역삼동' where memberName = '연아';

delete from memberTBL Where memberName = '연아';

create table deletedmembertbl(

        memberID char(8),

memberName char(5),

memberAddress char(20),

deleted date -- 삭제한 날짜

);

    delimiter //

create trigger trg_deletedMemberTBL -- 트리거이름

        after delete -- 삭제 후에 작동하게 지정

on memberTBL -- 트리거를 부착할 테이블

FOR EACH row

    begin

        -- OLD 테이블의 내용을 백업 테이블에 삽입

insert into deletedMemberTBL

            values (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE() );

    end //

delimiter ;

    select * from memberTBL;

delete from memberTBL where memberName = '당탕이';

select * from deletedMemberTBL;