CREATE TRIGGER `f1`.`ins_slot_tigger` BEFORE INSERT ON `f1`.`slot`
FOR EACH ROW BEGIN
if not exists(select * from t_slot sl where sl.rack_no = new.rack_no and sl.shelf_no = new.shelf_no and sl.slot_no =new.slot_no) then
insert into t_slot(EMS_CONNECTION_ID,NE_ID,RACK_ID,SHELF_ID,NAME,USER_LABEL,NATIVE_EMS_NAME,OWNER,ALARM_REPORTING_INDICATOR,HOLDER_STATE,LOCATION,VENDOR_NAME,HARD_WARE_VERSION,SERIAL_NO,RACK_NO,SHELF_NO,SLOT_NO,IS_DEL,CREATE_TIME,UPDATE_TIME)
values( NEW.EMS_CONNECTION_ID,NEW.NE_ID,NEW.RACK_ID,
NEW.SHELF_ID,NEW.NAME,NEW.USER_LABEL,
NEW.NATIVE_EMS_NAME,NEW.OWNER,NEW.ALARM_REPORTING_INDICATOR,
NEW.HOLDER_STATE,NEW.LOCATION,NEW.VENDOR_NAME,
NEW.HARD_WARE_VERSION,NEW.SERIAL_NO,NEW.RACK_NO,
NEW.SHELF_NO,NEW.SLOT_NO,NEW.IS_DEL,
NOW(),NEW.UPDATE_TIME);
else
update t_slot sl set sl.EMS_CONNECTION_ID = NEW.EMS_CONNECTION_ID,
sl.NE_ID = NEW.NE_ID,
sl.RACK_ID = NEW.RACK_ID,
sl.SHELF_ID= NEW.SHELF_ID,
sl.NAME = NEW.NAME,
sl.USER_LABEL = NEW.USER_LABEL,
sl.NATIVE_EMS_NAME = NEW.NATIVE_EMS_NAME,
sl.OWNER = NEW.OWNER,
sl.ALARM_REPORTING_INDICATOR = NEW.ALARM_REPORTING_INDICATOR,
sl.HOLDER_STATE = NEW.HOLDER_STATE,
sl.LOCATION = NEW.LOCATION,
sl.VENDOR_NAME = NEW.VENDOR_NAME,
sl.HARD_WARE_VERSION = NEW.HARD_WARE_VERSION,
sl.SERIAL_NO = NEW.SERIAL_NO,
sl.RACK_NO = NEW.RACK_NO,
sl.SHELF_NO = NEW.SHELF_NO,
sl.SLOT_NO = NEW.SLOT_NO,
sl.IS_DEL = NEW.IS_DEL,
sl.UPDATE_TIME = NOW()
where sl.rack_no = new.rack_no and sl.shelf_no = new.shelf_no and sl.slot_no =new.slot_no;
END if;
end //
FOR EACH ROW BEGIN
if not exists(select * from t_slot sl where sl.rack_no = new.rack_no and sl.shelf_no = new.shelf_no and sl.slot_no =new.slot_no) then
insert into t_slot(EMS_CONNECTION_ID,NE_ID,RACK_ID,SHELF_ID,NAME,USER_LABEL,NATIVE_EMS_NAME,OWNER,ALARM_REPORTING_INDICATOR,HOLDER_STATE,LOCATION,VENDOR_NAME,HARD_WARE_VERSION,SERIAL_NO,RACK_NO,SHELF_NO,SLOT_NO,IS_DEL,CREATE_TIME,UPDATE_TIME)
values( NEW.EMS_CONNECTION_ID,NEW.NE_ID,NEW.RACK_ID,
NEW.SHELF_ID,NEW.NAME,NEW.USER_LABEL,
NEW.NATIVE_EMS_NAME,NEW.OWNER,NEW.ALARM_REPORTING_INDICATOR,
NEW.HOLDER_STATE,NEW.LOCATION,NEW.VENDOR_NAME,
NEW.HARD_WARE_VERSION,NEW.SERIAL_NO,NEW.RACK_NO,
NEW.SHELF_NO,NEW.SLOT_NO,NEW.IS_DEL,
NOW(),NEW.UPDATE_TIME);
else
update t_slot sl set sl.EMS_CONNECTION_ID = NEW.EMS_CONNECTION_ID,
sl.NE_ID = NEW.NE_ID,
sl.RACK_ID = NEW.RACK_ID,
sl.SHELF_ID= NEW.SHELF_ID,
sl.NAME = NEW.NAME,
sl.USER_LABEL = NEW.USER_LABEL,
sl.NATIVE_EMS_NAME = NEW.NATIVE_EMS_NAME,
sl.OWNER = NEW.OWNER,
sl.ALARM_REPORTING_INDICATOR = NEW.ALARM_REPORTING_INDICATOR,
sl.HOLDER_STATE = NEW.HOLDER_STATE,
sl.LOCATION = NEW.LOCATION,
sl.VENDOR_NAME = NEW.VENDOR_NAME,
sl.HARD_WARE_VERSION = NEW.HARD_WARE_VERSION,
sl.SERIAL_NO = NEW.SERIAL_NO,
sl.RACK_NO = NEW.RACK_NO,
sl.SHELF_NO = NEW.SHELF_NO,
sl.SLOT_NO = NEW.SLOT_NO,
sl.IS_DEL = NEW.IS_DEL,
sl.UPDATE_TIME = NOW()
where sl.rack_no = new.rack_no and sl.shelf_no = new.shelf_no and sl.slot_no =new.slot_no;
END if;
end //
解决方案 »
- Toad for Mysql查询分析器
- web项目中对mysql服务监控问题
- mysql语句
- you have an error in your sql syntax;check the manual
- mysql 数据的异常问题
- postgresql 还原数据库问题
- 如何让delphi2006的dbexpress控件组访问mysql数据库?
- mysql数据库缺少读权限的情况下,怎么执行insert操作?
- *********真诚急招兼职Java程序员***********
- 如何统计一条数据有多少列?
- mysql查询后更新前10条数据问题。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
- Mysql查询到的值改变大小显示
然后create trigger