¡á¿¹ 5_3_1 || Æ®¸®°Å »ý¼º ¿¹
CREATE OR REPLACE TRIGGER chk_trig
BEFORE UPDATE
ON INSA
BEGIN
DBMS_OUTPUT.PUT_LINE('¿äûÇϽŠÀÛ¾÷ÀÌ Ã³¸® µÇ¾ú½À´Ï´Ù');
END;
/
¡á¿¹ 5_3_2 || Æ®¸®°Å ½ÇÇà ¿¹
UPDATE INSA SET SALARY=2800 WHERE SABUN='2013011103';
¡á¿¹ 5_3_3 || chk_row ¹é¾÷ Å×ÀÌºí »ý¼º
____CREATE TABLE____ chk_row (
SEQ_NO NUMBER,
TIME_IN DATE,
SABUN VARCHAR2(10),
ENG_NAME VARCHAR2(20),
COL_NAME VARCHAR2(10),
O_DATA VARCHAR2(10),
N_DATA VARCHAR2(10)
);
¡á¿¹ 5_3_4 || chk_row_udp Æ®¸®°Å »ý¼º
CREATE OR REPLACE TRIGGER chk_row_udp
BEFORE UPDATE OF SALARY
ON INSA
FOR EACH ROW
BEGIN
INSERT INTO chk_row (SEQ_NO, TIME_IN, SABUN, ENG_NAME,
COL_NAME, O_DATA, N_DATA)
VALUES (
(SELECT /*+ INDEX_DESC(CHK_ROW CHK_ROW_PK) */
DECODE(MAX(SEQ_NO),NULL,1,MAX(SEQ_NO)+1)
FROM CHK_ROW),
SYSDATE,
:OLD.SABUN,
:OLD.ENG_NAME,
'SALARY',
:OLD.SALARY,
:NEW.SALARY);
DBMS_OUTPUT.PUT_LINE(:OLD.ENG_NAME || '´ÔÀÇ ±Þ¿© Á¤º¸°¡ º¯°æµÇ¾ú½À´Ï´Ù.');
DBMS_OUTPUT.PUT_LINE('¼öÁ¤ Àü ±Ý¾× :' || :OLD.SALARY);
DBMS_OUTPUT.PUT_LINE('¼öÁ¤ ÈÄ ±Ý¾× :' || :NEW.SALARY);
END;
/
¡á¿¹ 5_3_5 || chk_row_upd Æ®¸®°Å µ¿ÀÛÀ» À§ÇÑ UPDATE ½ÇÇ๮
UPDATE INSA
SET SALARY=3550
WHERE ROWID IN (
SELECT ROWID
FROM INSA
WHERE JOIN_DAY LIKE '2002%'
AND JOIN_GBN_CODE ='RGL¡¯
);
¡á¿¹ 5_3_6 || Æ®¸®°ÅÀÇ »óŸ¦ Á¡°ËÇÏ´Â SQL
SELECT A.OWNER,A.TRIGGER_NAME,A.STATUS,B.STATUS
FROM ALL_TRIGGERS A, ALL_OBJECTS B
WHERE A.TRIGGER_NAME = B.OBJECT_NAME
--AND ( A.STATUS = 'DISABLED' OR B.STATUS = 'INVALID' )
AND A.OWNER = 'INSA'
AND TRIGGER_NAME = 'CHK_ROW_UDP';
¡á¿¹ 5_3_7 || CHK_ROWÅ×À̺íÀÇ Á¶È¸
SELECT * FROM CHK_ROW;
¡á¿¹ 5_3_8 || INSAÅ×À̺í Ä®·³º¯°æ
ALTER TABLE INSA RENAME COLUMN SALARY TO SALARY_IMSI;
¡á¿¹ 5_3_9 || Æ®¸®°ÅÀÇ »óŸ¦ Á¡°ËÇÏ´Â SQL
SELECT A.OWNER,A.TRIGGER_NAME,A.STATUS,B.STATUS
FROM ALL_TRIGGERS A, ALL_OBJECTS B
WHERE A.TRIGGER_NAME = B.OBJECT_NAME
--AND ( A.STATUS = 'DISABLED' OR B.STATUS = 'INVALID' )
AND A.OWNER = 'INSA'
AND TRIGGER_NAME = 'CHK_ROW_UDP';
¡á¿¹ 5_3_10 || INSAÅ×À̺í SALARY_IMSI°ª º¯°æ
UPDATE INSA
SET SALARY_IMSI=99999999
WHERE SABUN = '2010040101';
¡á¿¹ 5_3_10 || CHK_ROW_UDP Æ®¸®°Å 'DISABLE'
ALTER TRIGGER CHK_ROW_UDP DISABLE;
¡á¿¹ 5_3_11 || INSAÅ×À̺í UPDATE ½ÇÇà
UPDATE INSA
SET SALARY_IMSI=99999999
WHERE SABUN = '2010040101';
¡á¿¹ 5_3_12 || CHK_ROWÅ×À̺íÀÇ Á¶È¸
SELECT * FROM CHK_ROW;