--------------------------5.15-----------------------------------------------------
--트리거는 after와 instead of 가 있다.
create table backup_usertbl
(
useid char(8) not null primary key,
name nvarchar(10) not null,
birthday int not null,
addr nchar(2) not null,
mobile1 char(3),
mobile2 char(8),
height smallint,
mdate datetime,
modtype nchar(2),
moddate datetime,
moduser nvarchar(256)
)
select * from backup_usertbl
select * from usertbl
------------------------------------------
create trigger trg_backupusertbl
on usertbl
after delete, update
as
begin
declare @modtype nchar(2);
if(columns_updated() > 0) set @modtype ='수정'; --함수는 분홍색으로 표시됨, if update가 존재할시 1을 리턴해줌
else set @modtype='삭제'; --delete 아니면 update를 받는 트리거이므로 else면 delete겠지?
insert into backup_usertbl
select userid, name, birthyear, addr, mobile1, mobile2, height, mdate, @modtype, getdate(), user_name()
from deleted; --트리거와 관련된 테이블. deleted, inserted등 몇가지 종류가 있으며 임시 테이블임.
end;
--deleted 테이블은 윈도우의 휴지통과 같은 개념. 지워진 내용을 잠시 보관한다. update도 관련됨(지우고 다시 쓰므로)
--inserted 테이블도 역시 마찬가지로 insert 내용이 있을때 잠시 보관하는 역할을 함. update도 관련됨.(이유:동)
--update 시에는 수정되기 '이전의 값'이 저장된다. p687참고
------------------------------------------------
update usertbl set addr='대전' where userid='LKK' --update 연산을 해도 사용자는 알 수 없음. 관리자는 알 수 있다.
select * from backup_usertbl --트리거가 잘 됬는지 확인해보자. getdate(), user_name()은 날짜와 계정을 얻어주는 함수
--즉, 몇시에 누가 뭘(수정/삭제)를 했는지 알 수 있게 모니터할 수 있다
-----------------------------------------------
update usertbl set addr='서울' where name='김국징'
delete from usertbl where height='177' --포린키 제약조건에 걸려서 삭제 불가. 트리거에도 기록 안됨.
-----------------------------------------------
create view uv_deliver
as
select b.userid, u.name, b.prodname, b.price, b.amount, u.addr
from buytbl b, usertbl u
where b.userid=u.userid
select * from uv_deliver
----------------------------------------------
insert into uv_deliver
values ('JBI', '존만이', '구두', 50, 1, '인천') --안됨, 두개 테이블 뷰할때 인서트는 안됨. 위에도 언급.
--그러나 트리거를 이용해서 가능하게 만들 수 있다 o(^0^)o
--아래꺼 코딩한다음에 이거 한번 치면 실행이 된다~
----------------------------------------------
create trigger trg_insert
on uv_deliver
instead of insert --트리거는 수행되지만 인서트는 쌩깜. 즉 트리거만 수행. 주로 뷰에 사용
as
begin
insert into usertbl(userid, name, birthyear, addr, mdate) --1번 인서트
select userid, name, 1900, addr, getdate() --1900은 그냥 넣었다. not null이라..
from inserted;
insert into buytbl(userid, prodname, price, amount) --2번 인서트. 즉 인서트가 두개!
select userid, prodname, price, amount
from inserted;
end;
------------------------------------------
select * from uv_deliver
select * from usertbl
select * from buytbl
------------------------------------------
exec sp_help usertbl
exec sp_helptext uv_deliver --쿼리문 보여줌. 뷰를 만들어준 쿼리를 보여줌.
exec sp_helptext trg_insert
-----------------------------------------
drop trigger trg_backupusertbl --트리거 지우기
drop view uv_deliver --뷰나 테이블이 지워지면 그 속의 트리거도 같이 지워짐.
--이제부터 다시 11장 넘어와서 스토어드 프로지셔를 봅시다. 모듈식 프로그래밍이 가능하다.
--중요:성능을 향상시킬 수 있다. 보안을 강화시킬 수 있다. p626
create procedure usp_users --프로시저 생성
as
begin
select * from usertbl;
end
exec usp_users --프로시저 실행
--------------------------
create proc usp_users1
@username nvarchar(10) --매게변수는 declear안함. 지역변수만 디클레어
as
begin
select * from usertbl
where name=@username;
end
exec usp_users1 '이강규'
-----------------------------
create proc usp_users2
@userbirth int,
@userheight int
as
begin
select * from usertbl
where birthyear>@userbirth and height>@userheight;
end;
exec usp_users2 1970, 175 --매번 쿼리를 작성하지 않아도 매개변수만 주면 되므로 간단
-----------------------------
create proc usp_users3
@userbirth int=1970, --위 예제랑 똑같지만 값을 초기화하는것만 다르다.
@userheight int=178 --즉 이것은 디폴트를 명시하는것.
as
begin
select * from usertbl
where birthyear>@userbirth and height>@userheight;
end;
exec usp_users3 1970 --지금 값을 하나만 주고 height는 안줬다. 그럴땐 그냥 178로 디폴트.
drop proc usp_users3 --프로시저 지우기
-----------------------------
--p630 그림봐라. 스토어 프로시저를 사용하면 왜 보안과 성능이 좋아지는지 살펴보자.
--일단 T-SQL의 동작 구조부터 보십시다.
1.구문분석:문법검사.
2.개체이름확인:구문은 맞지만 참조하는 개체가 있는지 없는지를 확인(usertb등)
3.권한:요청자가 그 쿼리를 실행할 수 있는지 아닌지 권한을 확인.
4.최적화:어떻게 실행하면 '빠르게' 처리할 수 있는지를 고민. index를 사용할지 말지.
5.컴파일 및 실행계획:고급어->기계어('★메모리에 캐시' 두번째 빠르게 할려고)
-->스토어드 프로시저는 똑같은 문장을 불러오므로 같은 패턴을 반복하면 시간이 빠르다.
-->쿼리가 프로시저 안으로 숨으므로 보안성 좋아짐