…·─━☆ 무한코딩세계

트리거 로그 메시지 남기기

- 로그 메시지를 남기기 위해 테이블을 하나 생성하고 트리거에서의 컬럼이름과 이떄 테이블의 컬럼이름을 같게 해야한다.

- 트리거를 이용하여 해당 테이블에 접근을 할 때 로그 메시지 테이블에 접근한 정보를 삽입하므로써 메시지를 남길 수 있다.

로그 메시지를 남길 m_log 테이블 생성

SQL> create table m_log
  2  (
  3  m_who varchar2(10),
  4  m_action varchar2(100),
  5  m_when date
  6  );

테이블이 생성되었습니다.

트리거 생성

SQL>
SQL> create or replace trigger w_action
  2   before insert or update or delete on student
  3   declare
  4      l_action  m_log.m_action%type;
  5   begin
  6     if INSERTING then l_action := 'Insert';
  7     elsif UPDATING then l_action := 'Update';
  8     elsif DELETING then l_action := 'Delete';
  9     end if;
 10     insert into m_log (m_who, m_action, m_when)
 11     values(user, l_action, sysdate);
 12   end;
 13  /

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

student 티이블에 데이터 삽입 명령실행

insert into student values( '100', 'tirgeer', 'seoul', to_date('1974-07-09', 'YYYY-MM-DD'), '1');

SQL> @trigger4.sql

1 개의 행이 만들어졌습니다.

m_log에서 로그 메시지 확인

SQL> select * from m_log;

M_WHO
--------------------
M_ACTION
--------------------------------------------------------------------------------
M_WHEN
--------
S4406144
Insert
07/11/13

SQL>

트리거 삭제

drop trigger m_log;

트리거 생성 에러 발생시 에러 메시지 보기

show errors

-- 기존의 대여 테이블

CREATE TABLE lending (
 idx  INT IDENTITY(0,1),
 bookidx  INT NOT NULL,  // ISBN
 useridx  INT NOT NULL,  // 사용자 ID
 returnplan SMALLDATETIME NOT NULL, // 예정반납일   ----
 lendday  SMALLDATETIME NOT NULL, // 대출일
 PRIMARY KEY (idx)
 UNIQUE (bookidx, useridx, lendday),
 FOREIGN KEY (bookidx) REFERENCES book (idx),
 FOREIGN KEY (useridx) REFERENCES userdb (idx)
)

-- 기존의 대여했던 도서의 테이블

CREATE TABLE lendhistory (
 bookidx  INT NOT NULL,
 useridx  INT NOT NULL,
 username VARCHAR(10) NOT NULL,
 userseason DECIMAL(3,0) NOT NULL,
 typecode INT NOT NULL,
 bookname VARCHAR(100) NOT NULL,
 publisher VARCHAR(50) NOT NULL,
 author  VARCHAR(50) NOT NULL,
 returnplan SMALLDATETIME NOT NULL,
 lendday  SMALLDATETIME NOT NULL,
 returnday SMALLDATETIME NOT NULL, // 실제반납일
 PRIMARY KEY (bookidx, useridx)
)
 
--

select username, count(*) AS m_count
from
(
select useridx, username
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 11
union all
select useridx, username
 from lending
  where year(lendday) = 2007 and month(lendday) = 11
) as a1
group by username
order by 2 desc

-- 쿼리 문

select * from lending where useridx = 67 and year(lendday) = 2007 and month(lendday) = 10

select count(*) AS counter from lending where useridx = 67 and year(lendday) = 2007 and month(lendday) = 10

/*
create view ssss
 select useridx, count(*) as num_prods
  from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
  group by useridx
  order by 2 desc
*/
/*
select useridx, count(*) as num_prods
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
  group by useridx
  order by 2 desc
*/

/*
select *
from lending
where
 year(lendday) = 2007 and month(lendday) = 10
 or
 useridx in (select useridx
     from lendhistory
     where year(lendday) = 2007 and month(lendday) = 10 )

 --from lending
*/

select lendhistory.useridx, count(*) as num_prods
 from lendhistory
  left join lending
   on lendhistory.useridx = lending.useridx
  where year(lendhistory.lendday) = 2007 and month(lendhistory.lendday) = 10
    or year(lending.lendday) = 2007 and month(lending.lendday) = 10
  group by lendhistory.useridx
  order by 2 desc

/*
select *--lendhistory.useridx, count(*) as num_prods
 from lendhistory full outer join lending
  on lendhistory.useridx = lending.useridx
  where
   ((year(lendhistory.lendday) = 2007 and month(lendhistory.lendday) = 10)
     or ((year(lending.lendday) = 2007 and month(lending.lendday) = 10)))
  --group by lendhistory.useridx
  --order by 2 desc
*/
/*
select useridx, count(*)
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
group by useridx
*/

-- 최종 1

select useridx, count(*)
from
(
select useridx, username
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
union all
select useridx, 'aa'
 from lending
  where year(lendday) = 2007 and month(lendday) = 10
) as a1
group by useridx
order by 2 desc

/*
create view ssss
 select useridx, count(*) as num_prods
  from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
  group by useridx
  order by 2 desc
*/
/*
select useridx, count(*) as num_prods
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
  group by useridx
  order by 2 desc
*/

select useridx, count(*) AS m_count
from
(
select useridx, username
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
union all
select useridx, ''
 from lending
  where year(lendday) = 2007 and month(lendday) = 10
) as a1
group by useridx
order by 2 desc

/*
insert into test from  (
 select useridx
  from lendhistory
   where year(lendday) = 2007 and month(lendday) = 10
 union all
 select useridx
  from lending
   where year(lendday) = 2007 and month(lendday) = 10
)
*/
/*
select *
from lending
where
 year(lendday) = 2007 and month(lendday) = 10
 or
 useridx in (select useridx
     from lendhistory
     where year(lendday) = 2007 and month(lendday) = 10 )

 --from lending
*/
/*
select lendhistory.useridx, count(*) as num_prods
 from lendhistory
  left join lending
   on lendhistory.useridx = lending.useridx
  where year(lendhistory.lendday) = 2007 and month(lendhistory.lendday) = 10
    or year(lending.lendday) = 2007 and month(lending.lendday) = 10
  group by lendhistory.useridx
  order by 2 desc
*/

/*
select *--lendhistory.useridx, count(*) as num_prods
 from lendhistory full outer join lending
  on lendhistory.useridx = lending.useridx
  where
   ((year(lendhistory.lendday) = 2007 and month(lendhistory.lendday) = 10)
     or ((year(lending.lendday) = 2007 and month(lending.lendday) = 10)))
  --group by lendhistory.useridx
  --order by 2 desc
*/
/*
select useridx, count(*)
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
group by useridx
*/

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Hwang Jun - Beom
-- Create date: 2007/03/23
-- Description: input lendhistory
-- =============================================
CREATE PROCEDURE lendreturn_sp
 -- Add the parameters for the stored procedure here
 @idx  VARCHAR(15)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 DECLARE @d_returndate SMALLDATETIME;
 DECLARE @i_bookidx INT;

 SELECT @i_bookidx = bookidx FROM lendinginfo_view WHERE idx = @idx;
 SELECT @d_returndate = returndate FROM reservation WHERE bookidx = @i_bookidx;

 IF @d_returndate IS NULL
  BEGIN
   UPDATE reservation SET returndate = GETDATE() WHERE bookidx = @i_bookidx;
  END

 INSERT INTO lendhistory SELECT bookidx, useridx, username, season, type, bookname, publisher, author, returnplan, lendday, returnday FROM input_lendhistory_view WHERE idx = @idx;
 DELETE FROM lending WHERE idx = @idx;

END
GO