使用Oracle创建触发器时出现问题

本文介绍了使用Oracle创建触发器时出现问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的问题是:

触发器，该触发器自动将销售代理存储在称为"ExcellentSale"的单独表中 名称，汽车型号和制造商名称，每次商定的价格 SalesTransaction是汽车要价的20％以上. (注意:您需要创建 实施此触发器之前，请先点击"ExcellentSale"表.要创建主键，请使用 从1开始并以1递增的顺序.

A Trigger which automatically stores in a separate table called ‘ExcellentSale’ the Sales Agent name, car model and manufacturer name, each time the agreed price of a SalesTransaction is more than 20% of the car’s asking price. (Note: You need to create the ‘ExcellentSale’ table before implementing this trigger. To create the primary key, use a sequence that starts at 1 and increments by 1).

我正在使用这些表格

Manufacturer(manufacturerID, name, region) Model(modelNo, name, type, previousModel, manufacturerID) Car(VIN, dateAcquired, yearBuilt, purchasedPrice, askingPrice, currentMileage, modelNo) SalesAgent(agentID, name, DOB) SalesTransaction(VIN, custID, agentID, dateOfSale, agreedPrice)

这是我的尝试

create sequence ggenerateKey start with 1 increment by 1; CREATE TABLE ExcellentSale( recordNo NUMBER, agentName VARCHAR2(20) NOT NULL, modelName VARCHAR2(20) NOT NULL, manufacturerName VARCHAR2(20) NOT NULL, PRIMARY KEY(recordNo)); create or replace trigger AutoStore before insert on SalesTransaction for each row declare agentName varchar2(50); modelName varchar2(50); manufacturerName varchar2(50); askingprice number; agreedprice number; begin select sa.name, mo.name, mu.name, c.askingprice, st.agreedprice into agentName, modelName, manufacturerName, askingprice, agreedprice from manufacturer MU, Model MO, Car C, SalesAgent SA, SalesTransaction ST where mu.manufacturerid = mo.manufacturerid and st.vin = c.vin AND c.vin = :new.vin AND sa.agentID = :new.agentID; IF :new.agreedPrice > (1.2 * askingPrice) THEN INSERT INTO ExcellentSale VALUES (ggenerateKey.nextval, agentName, modelName, manufacturerName); END IF; end AutoStore; /

触发器被编译，当我尝试对其进行测试时，我使用将插入到SalesTransaction中的这些值，然后应触发触发器，但显示为错误.

The trigger compiles and when I try to test this I use these values that will be inserted into SalesTransaction, this should then fire the trigger but shows as an error.

insert into SalesTransaction values ('2B7JB33R9CK683376', '1', '1', to_date('01-02-2013','dd-mm-yyyy'), 586000 );

显示的错误是这个

insert into SalesTransaction * ERROR at line 1: ORA-01403: no data found ORA-06512: at "JTLA.AUTOSTORE", line 8 ORA-04088: error during execution of trigger 'JTLA.AUTOSTORE'

推荐答案

此错误意味着带有"into"子句的select语句不产生任何行. 为避免此错误，我通常在每一列上使用聚合函数MAX()，以保证1行结果集，在无匹配行"的情况下生成空值，并且不会引发异常

This error means that your select statement with "into" clause produces no rows. To avoid this error, I usualy use aggregate function MAX() on each column, it garante 1 row result set, generates null values in case of 'no matching rows', and no exception rised