新建表
CREATE TABLE SCHEDULES
(
SCHEDULES_ID NUMBER() DEFAULT NULL NOT NULL,
USER_ID NUMBER() DEFAULT NULL NOT NULL
CONSTRAINT SCHEDULES_USERS_USER_ID_FK
REFERENCES USERS
ON DELETE CASCADE,
DATE_TIME DATE DEFAULT NULL NOT NULL,
EVENT VARCHAR2( CHAR) DEFAULT NULL NOT NULL,
CONSTRAINT SCHEDULES_ID_USER_ID_PK
PRIMARY KEY (SCHEDULES_ID, USER_ID)
)
SCHEDULES_ID和USER_ID是联合主键
**要实现的是:**schedules_id根据user_id的不同自增,例如在user_id=1中,schedules_id最大是3,那么新插入一条user_id=1的记录,使schedules_id=4.user_id=2中,schedules_id最大是5,那么新插入一条user_id=2的记录,使schedules_id=6.
create or replace trigger tri_scheduleid_insert
before insert on SCHEDULES
for each row
DECLARE
begin
select MAX(SCHEDULES_ID)+ into :new.SCHEDULES_ID from SCHEDULES WHERE USER_ID=:new.USER_ID;
end tri_scheduleid_insert;
直接使用触发器实现,不用创建sequence。
但是当MAX(SCHEDULES_ID)不存在时,会无法插入
修改后触发器的创建是
CREATE OR REPLACE TRIGGER tri_scheduleid_insert
BEFORE INSERT ON SCHEDULES
FOR EACH ROW
DECLARE
nextid NUMBER;
BEGIN
SELECT MAX(SCHEDULES_ID) +
INTO nextid
FROM SCHEDULES
WHERE USER_ID = :new.USER_ID;
IF INSERTING
THEN
IF nextid >
THEN
:new.SCHEDULES_ID := nextid;
ELSE :NEW.SCHEDULES_ID := ;
END IF;
END IF;
END tri_scheduleid_insert;
首先定义一个变量nextid来保存查询得到的最大值+1,如果nextid大于0,则说明插入记录的user_id的值存在,schedules_id=nextid;否则
说明插入记录的user_id的值不存在,使schedules_id=1;
注意: :new.SCHEDULES_ID := nextid;
赋值等号左边的“:”
同样的思考应该可以不创建sequence来创建触发器实现主键的自增。我没有做,毕竟创建sequence还是有好处的。这里联合主键的自增我没找到有关sequence的实现。如果有更好的办法请留下你的建议。