Create trigger

麻煩請各位高手指點一下,謝謝

Step 1) Create a table min_sal as follows.

CREATE TABLE min_sal

(

job VARCHAR2(9) PRIMARY KEY,

min_sal NUMBER(7, 2) NOT NULL

);

Step 2) Populate the min_sal table as follows.

INSERT INTO min_sal VALUES ('ANALYST', 3000);

INSERT INTO min_sal VALUES ('CLERK', 800);

INSERT INTO min_sal VALUES ('MANAGER', 2400);

INSERT INTO min_sal VALUES ('PRESIDENT', 5000);

INSERT INTO min_sal VALUES ('SALESMAN', 1200);

INSERT INTO min_sal VALUES ('OTHERS', 500);

COMMIT;

Step 3)

Create a trigger called emp_sal_trg on the emp table. When an INSERT or UPDATE statement is issued against the emp table, the trigger is fired to ensure that the value of the SAL field meets the criteria in the min_sal table.

Update:

謝謝你的回答。

再請問,我參考你所建立的Oracle Trigger,

可是我卻跑不出來

Update 2:

CREATE OR REPLACE TRIGGER emp_sal_trg

BEFORE INSERT OR UPDATE OF min_sal ON emp

FOR EACH ROW

BEGIN

:NEW.min_sal := UPPER(:NEW.min_sal);

END;

Error report:

ORA-00904: "MIN_SAL": invalid identifier

00904. 00000 - "%s: invalid identifier"

*Cause:

*Action:

1 Answer

Rating
  • Anonymous
    1 decade ago
    Favorite Answer

    建立MS SQL Trigger

    CREATE TRIGGER emp_sal_trg ON emp

    FOR INSERT,Update

    AS

    declare @min_sal_new NUMBER(7, 2),@min_sal_old NUMBER(7, 2)

    --取得Insert update的min_sal new old值

    set @min_sal_new=(SELECT ins.[min_sal] FROM inserted ins)

    set @min_sal_old=(SELECT del.[min_sal] FROM deleted del)

    --也可以針對特定欄位的Update作程序

    IF UPDATE(min_sal)

    begin

    --criteria

    end

    建立Oracel Trigger

    create or replace trigger emp_sal_trg

    before insert or update of min_sal on emp

    referencing old as old_value

    new as new_value

    for each row

    --old值:old_value.min_sal

    --new值:new_value.min_sal

    when (條件)

    begin

    --criteria

    end;

    但DB會因為Trigger多少影響一些效能

    所以要適當處理

    詳細請參考

    http://asp.godhelp.com.tw/2009/11/create-trigger.h...

    Source(s): GH教學
Still have questions? Get your answers by asking now.