Q.编写一个数据库触发器以显示当任何时候雇员加薪时的加薪情况。 A. CREATE OR REPLACE TRIGGER emp_SalUp AFTER UPDATE OF sal ON emp FOR EACH ROW DECLARE vsal NUMBER; BEGIN vsal:=NVL(:NEW.sal,0)-NVL(:OLD.sal,0); IF vsal<=0 THEN RAISE_APPLICATION_ERROR(-20001,'Increased Salary is not zero and littler than zero'); END IF; END; / Q.编写一个数据库触发器,它允许用户只在上午9.00到下午5.00之间执行DML任务。 A. CREATE OR REPLACE TRIGGER operate_time_limited BEFORE INSERT OR UPDATE OR DELETE ON emp --FOR EACH ROW DECLARE vtime NUMBER; BEGIN vtime:=TO_NUMBER(TO_CHAR(SYSDATE,'HH24')); IF vtime NOT BETWEEN 9 AND 17 THEN RAISE_APPLICATION_ERROR(-20444,'Sorry!Not Except 9AM and 5PM.'); END IF; END; / Q.编写一个数据为触发器以检查某个组织中不能有两个总裁。 A. CREATE OR REPLACE TRIGGER check_president BEFORE INSERT OR UPDATE ON emp FOR EACH ROW WHEN (UPPER(NEW.job)='PRESIDENT') DECLARE vCount NUMBER; BEGIN SELECT COUNT(job) INTO vCount FROM emp WHERE UPPER(job)='PRESIDENT'; --把总统的个数统计出来,当为0时,变量值为0IF vCount>0 THEN RAISE_APPLICATION_ERROR(-20444,'Sorry!Can''t have two President.'); END IF; END; / Q.编写一个数据库触发器,当任何时候某个部门从"dept"中删除时,该触发器将从"emp"表中删除该部门的所有雇员。 A. CREATE OR REPLACE TRIGGER del_emp_deptno BEFORE DELETE ON dept FOR EACH ROW BEGIN DELETE FROM emp WHERE deptno=:OLD.deptno; END; /
A.
CREATE OR REPLACE TRIGGER emp_SalUp
AFTER UPDATE OF sal ON emp
FOR EACH ROW
DECLARE
vsal NUMBER;
BEGIN
vsal:=NVL(:NEW.sal,0)-NVL(:OLD.sal,0);
IF vsal<=0 THEN
RAISE_APPLICATION_ERROR(-20001,'Increased Salary is not zero and littler than zero');
END IF;
END;
/
Q.编写一个数据库触发器,它允许用户只在上午9.00到下午5.00之间执行DML任务。
A.
CREATE OR REPLACE TRIGGER operate_time_limited
BEFORE INSERT OR UPDATE OR DELETE ON emp
--FOR EACH ROW
DECLARE
vtime NUMBER;
BEGIN
vtime:=TO_NUMBER(TO_CHAR(SYSDATE,'HH24'));
IF vtime NOT BETWEEN 9 AND 17 THEN
RAISE_APPLICATION_ERROR(-20444,'Sorry!Not Except 9AM and 5PM.');
END IF;
END;
/
Q.编写一个数据为触发器以检查某个组织中不能有两个总裁。
A.
CREATE OR REPLACE TRIGGER check_president
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (UPPER(NEW.job)='PRESIDENT')
DECLARE
vCount NUMBER;
BEGIN
SELECT COUNT(job) INTO vCount
FROM emp
WHERE UPPER(job)='PRESIDENT'; --把总统的个数统计出来,当为0时,变量值为0IF vCount>0 THEN
RAISE_APPLICATION_ERROR(-20444,'Sorry!Can''t have two President.');
END IF;
END;
/
Q.编写一个数据库触发器,当任何时候某个部门从"dept"中删除时,该触发器将从"emp"表中删除该部门的所有雇员。
A.
CREATE OR REPLACE TRIGGER del_emp_deptno
BEFORE DELETE ON dept
FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno=:OLD.deptno;
END;
/