힐링 ASMR 과 프로그래밍

트리거란(trigger)?

트리거(trigger)는 테이블에 대해 어떠한 처리를 실행하면 이에 반응하여 설정해 둔 명령이 자동으로 실행되는 구조를 말합니다. 이때, 이러한 과정이 총의 방아쇠를 당기는 것과 같다 하여 트리거(방아쇠)라고 합니다.

Insert나 update, delete등의 명령이 실행될 때, 사전에 트리거로 설정해 놓은 기능도 함께 실행할 수가 있습니다. 예를 들어, 테이블의 레코드를 변경하면 그것을 계기로 변경한 내용을 다른 테이블에 기록하도록 트리거를 작성할 수 있습니다.

예제로 테이블에 있는 레코드를 삭제하면, 삭제한 레코드가 다른 테이블에 복사되는 트리거를 만들어 보겠습다.

테이블 컬럼 구조만 복사해서 table_trigger 테이블을 만들어 둡니다.

select * from table_03;

create table table_trigger like table_03;

select * from table_trigger;

트리거 만들기.

트리거는 insert, update, delete등의 명령이 실행되기 직전이나 직후에 호출되어 실행됩니다.

또한 테이블에서 어떠한 처리를 하기 직전과 직후의 값은 다음과 같이 old.컬럼명 과 new.컬럼명으로 얻을 수 있습니다.

다음은 크리거를 만드는 명령문입니다.

create trigger 트리거명 before(또는 after) delete 등의 명령

on 테이블명 for each row

begin

           변경전(old.컬럼명) 또는 변경후(new.컬럼명)을 이용한 처리

end

트리거의 본체를 작성할 때 각 명령의 끝에는 세미콜론을 입력합니다. 따라서 미리 구분 문자를 //로 변경해 둡니다.

테이블 table_03의 레코드를 삭제하고, 삭제한 레코드를 테이블 table_trigger에 삽입하는 트리거 trigger_01을 작성해서 테이블 table_03에 있는 모든 레코드를 삭제해 보겠습니다.

delimiter //

create trigger trigger_01

before delete on table_03 for each row

begin

insert into table_trigger values(old.number, old.name, old.age);

end

//

delimiter ;

트리거가 생성되었습니다.

이제 테이블 table_03에 레코드를 모두 삭제하여 table_trigger에 삭제된 레코드가 저절로 입력되는지 확인해 보겠습니다.

delete from table_03;

select * from table_03;

select * from table_trigger;

table_03에는 레코드가 남아 있지 않고 table_trigger에는 삭제된 모든 레코드가 추가되어 있어 트리거가 잘 동작한 것을 확인할 수 있습니다.

설정한 트리거 확인하기.

현재 설정된 트리거를 확인할 때에는 다음 명령어를 사용합니다.

show triggers;

트리거 삭제하기.

트리거를 삭제할때는 다음 명령어를 사용합니다.

drop trigger 트리거명;

drop trigger trigger_01;

show triggers;

MySQL 저장 프로시저 활용하기.

저장 프로시저란 무엇인가?

여러 SQL 문을 하나의 SQL 문처럼 정리하여 call 명령으로 실행할 수 있게 만든 것을 저장 프로시저라고 합니다. 저장 프로시저는 일련의 절차를 정리해서 저장한 것입니다.

사전에 준비해 둔 많은 명령을 자동으로 실행할 수 있기 때문에 작업의 효율성도 높일 수 있습니다.

저장 프로시저 만들기.

저장 프로시저를 작성할 때에는 다음과 같이 create procedure라는 명령을 사용합니다.

create procedure 저장 프로시저명( )

begin

           sql 문1;

           sql 문2;

end

begin 에서 end 까지가 저장 프로시저의 본체입니다.

시작에 begin을 끝에 end를 붙여 저장 프로시저의 명령 범위를 확실히 하고 있습니다.

저장 프로시저의 본체는 평범한 sql문 입니다. 그래서 끝에 종료를 나타내는 세미콜론을 입력해야 합니다. 하지만 이렇게 되면 저장 프로시저를 작성하는 중간에 세미콜론을 입력하게 되어서 저장 프로시저가 완성되지 않은 상태에서 실행이 됩니다. 세미콜론이 입력되면 어떤 경우에서 건 일단 세미콜론 이전 단계까지 명령문을 실행하게 됩니다.

구분 문자(;) 변경하기.

명령문이 완성되지 않은 상태에서 실행되면 곤란합니다. 저장 프로시저에서 end를 입력하고 나서 프로시저 명령이 실행되도록 환경을 변경해야 합니다.

그러려면 저장 프로시저를 작성하기 전에 구분 문자를 세미콜론이 아닌 다른 문자로 변경해 둡니다. 일반적으로 //을 사용합니다.

구분 문자를 //으로 변경할 때에는 delimiter명령을 사용합니다.

delimiter //

구분 문자를 //으로 변경해 두면 저장 프로시저를 작성하는 도중에 세미콜론을 입력해도 문제없습니다. End 뒤에 //을 입력하면 create procedure 명령이 실행됩니다.

저장 프로시저를 모두 작성했으면 delimiter ; 로 구분 문자를 원래대로 되돌려 놓습니다.

프로시저를 호출할 때는 call 프로시저명; 을 사용합니다.

select * from table_sales; 와 select * from table_member을 실행하는 저장 프로시저를 생성하겠습니다.

delimiter //

create procedure procedure_01( )

begin

select * from table_sales;

select * from table_member;

end

//

delimiter ;

call procedure_01;

설정한 값 이상인 레코드만 표시하는 저장 프로시저 만들기.

인수를 대입해서 실행하는 저장 프로시저를 만들어 보겠습니다.

처리하고자 하는 데이터를 괄호( ) 안에 대입해서 저장 프로시저를 실행합니다.

다음은 저장 프로시저에 인수를 대입하는 방법입니다.

procedure 저장프로시저명(인수명 자료형)

table_sales에 sales가 인수 s 이상인 레코드를 표시하는 프로시저를 생성해서 호출해 보겠습니다.

delimiter //

create procedure procedure_02(s int)

begin

select * from table_sales where sales >= s;

end

//

delimiter ;

call procedure_02(100);

저장 프로시저의 내용 표시하기.

작성한 저장 프로시저의 내용을 표시할 때에는 다음 명령을 실행합니다.

show create procedure 저장프로시저명;

show create procedure procedure_01;

저장 프로시저 삭제하기.

저장 프로시저를 삭제할 때는 데이터베이스나 테이블, 뷰를 삭제할 때와 마찬가지로 drop 명령을 사용합니다.

명령문은 다음과 같습니다.

drop procedure 저장프로시저명;

drop procedure procedure_01;