SQL - 제약과 트리거 2

이전 포스터에서 닭이 먼저냐 달걀이 먼저냐와 같은 문제에 접하게 되었고 이어서 살펴보도록 하자. 

---

Exec 테이블에서 studio의 presCNo를 참조하고 있고 studio에서는 Exec의 certNo를 참조하고 있기 때문이다. 처음 테이블이 만들어진 경우 두 테이블 모두 비어있기 때문에 어떤 값도 넣어줄 수 없는 상황이 오는 것이다.

값을 추가하려고 시도하면 위와 같이 참조키 제약 조건을 위배했다고 추가되지 않음을 확인할 수 있다. 가장 간단한 방법은 참조키를 선언하지 않고 만들고 alter로 이후에 조건을 주는 것이고 이외에도 DEFERRABLE을 선언해 확인 작업을 뒤로 미룰 수 있다.

DEFERRABLE [INITIALLY IMMEDIATE]

DEFERRABLE INITIALLY DEFERRED (뒤로 미루는 것)

그럼 언제 체크를 하냐? 트랜잭션이 성공적으로 끝나기 직전에 체크를 하는 것이다. 값을 넣어놓고 확인을 시작하면 빈 테이블로는 만들어지지 않기 때문이다.

 

START TRANSACTION;

SET CONSTRAINTS presRef DEFERRED;

-- Acutal updates

COMMIT;

 

위와 같이 사용하고 조건 체크는 commit 전에 이루어지게 되는 것이다.

PRIMARY KEY를 제외한 값들에 기본적으로 null값이 허용되지만 제약조건을 추가해 금지시킬 수도 있다. 이것이 NOT NULL이다.

 

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,

address VARCHAR(255),

presCNo INT REFERENCES MovieExec(certNo) NOT NULL

);

 

이와 같이 사용할 수 있다. 뿐만 아니라 check 조건을 주어 해당 조건에 만족하는 값만 추가해줄 수 있다.

 

CREATE TABLE Studio (

name CHAR(50),

address VARCHAR(255),

presCNo INT REFERENCES MovieExec(certNo)

CHECK (presCNo >= 100000),

PRIMARY KEY (name)

);

 

PresCNo가 100000가 넘어야하는 check 조건이다. 조건이 잘 작동하는 지 실제 실습을 통해 확인해보았다.

위와 같은 형태로 진행을 하려고 했으나 애초에 prescno가 100000보다 큰 값이 있지 않아 확인이 불가능할 것 같았다. 따라서 check 조건을 200으로 수정해 진행해보았다.

위와 같이 잘 입력이 되는 것을 볼 수 있고 실제로 잘 들어갔는지 확인도 해보았다.

CREATE TABLE MovieStar (

name CHAR(30) PRIMARY KEY,

address VARCHAR(255),

gender CHAR(1),

birthdate DATE,

CHECK (gender = 'F' OR name NOT LIKE 'Ms.%')

);

위와 같은 방식으로도 사용할 수 있는데 check문의 뜻은 gender가 F가 아닌 경우에는 이름이 MS.으로 시작해서는 안 된다 이다.

 

<Assertion>

Syntax : CREATE ASSERTION <name> CHECK (<condition>);

해당 assertion이 속하는 모든 데이터베이스의 변경에 대해 check문이 true가 되는 지 판별한다. 스피드가 많이 느려질 가능성이 있고 많은 DB서버가 지원하지 않는 것이다.

 

<Oracle Trigger>

• Event = a class of changes in the DB, e.g., “insert into Movie.” (어떤 변경, 이벤트가 발생 했을 때)

• Condition = a test as in a where-clause for whether or not the trigger applies. (조건이 만족하는 지)

• Action = one or more SQL statements. (이벤트가 일어나고 조건이 만족하면 sql 문장이 실행된다. )

 

예제로 분석을 해보겠다.

CREATE OR REPLACE TRIGGER MovieExecTrig

AFTER INSERT ON Studio

FOR EACH ROW

WHEN (new.presCNo > 10000)

BEGIN

INSERT INTO MovieExec(certNo)

VALUES(:new.presCNo);

END;

/

여기서의 event는 INSERT ON Studio 이기 때문에 studio에 insert하려고 하는 경우를 말한다. For each row는 10개의 값이 입력이 되려고 하면 그 때 그 값들 각각에 대해서 조건을 확인하는 과정이다. when 문장은 조건인 것은 앞서 학습을 했었고 ()안에 new는 새로운 값을 의미한다. old도 존재하는데 delte의 경우 이전 값을 삭제하는 것이므로 old만이 존재하고 insert의 경우 새로운 값을 입력하는 것이기 때문에 new만 존재한다. update의 경우 old와 new 값 모두 존재한다.

 

<trigger in postgreSQL>

* 현재 사용자의 이름과 시간은 마지막 사용자 및 마지막 날짜로 행에 스탬프로 표시된다.

* 다음을 체크하는데 1.직원 명칭 부여, 2.급여가 플러스인지 확인

 

위와 같은 조건이 주어졌을 때 postgreSQL에서는 밑에서와 같이 활용할 수 있다.

CREATE TABLE emp (

empname text,

salary integer,

last date timestamp,

last user text

);

 

CREATE FUNCTION emp stamp() RETURNS trigger AS $$

    BEGIN

– Check that empname and salary are given

IF NEW.empname IS NULL THEN  (직원 명칭 null체크)

RAISE EXCEPTION ’empname cannot be null’;

END IF;

IF NEW.salary IS NULL THEN   (직원 월급 null체크)

RAISE EXCEPTION ’% cannot have null salary’, NEW.empname;

END IF;

– Who works for us when she must pay for it?

IF NEW.salary < 0 THEN   (직원 월급 양수인지 체크)

RAISE EXCEPTION ’% cannot have a negative salary’, NEW.empname;

END IF;

– Remember who changed the payroll when

NEW.last date := current timestamp;   (조건1)

NEW.last user := current user;         (조건1)

RETURN NEW;

    END;

$$ LANGUAGE plpgsql;

 

CREATE TRIGGER emp stamp

BEFORE INSERT OR UPDATE ON emp

FOR EACH ROW

EXECUTE PROCEDURE emp stamp();

 

INSERT INTO emp VALUES (’Katie Couric’, NULL, NULL, NULL);

INSERT INTO emp VALUES (’Katie Couric’, 5000, NULL, NULL);

 

Another>

앞서 emp에 넣었던 값도 같이 확인 되는 것을 볼 수 있고 원하는 정보로 잘 들어갔음을 확인해볼 수 있다.