메모)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;
댓글