我平时用oracle,今天用了下mysql,现在有个触发器问题请各位帮帮忙
CREATE OR REPLACE TRIGGER TIB_SNMP
BEFORE INSERT
ON SNMP
FOR EACH ROW
begin
declare n int;
declare temp_uk_id varchar(64);
declare v_uk_id varchar(64);
declare v_station_id varchar(4);
declare v_RECORD_ID varchar(4);
declare v_channel_id varchar(4);
declare v_SUBDEVICE_ID varchar(8);
declare v_RECVSUBDEV_ID varchar(8);
declare v_PROTOTYPE varchar(1);
declare v_PROTOCOLID varchar(2);
declare v_EVALTYPE varchar(2);
declare v_SEC varchar(10);
declare v_MSEC varchar(6);
declare m int;
declare v_m varchar(4);
declare v_errorcode int;
declare v_errortext varchar(200); SELECT LPAD (new.station_id, 4, '0') INTO v_station_id FROM DUAL;
SELECT LPAD (new.RECORD_ID, 4, '0') INTO v_RECORD_ID FROM DUAL;
SELECT LPAD (new.channel_id, 4, '0') INTO v_channel_id FROM DUAL;
SELECT LPAD (new.SENDSUBDEV_ID, 8, '0') INTO v_SUBDEVICE_ID FROM DUAL;
SELECT LPAD (new.RECVSUBDEV_ID, 8, '0') INTO v_RECVSUBDEV_ID FROM DUAL;
SELECT LPAD (new.PROTOTYPE, 1, '0') INTO v_PROTOTYPE FROM DUAL;
SELECT LPAD (new.PROTOCOLID, 2, '0') INTO v_PROTOCOLID FROM DUAL;
SELECT LPAD (new.EVALTYPE, 2, '0') INTO v_EVALTYPE FROM DUAL;
SELECT LPAD (new.SEC, 10, '0') INTO v_SEC FROM DUAL;
SELECT LPAD (new.MSEC, 6, '0') INTO v_MSEC FROM DUAL;
temp_uk_id :=
v_station_id
|| v_RECORD_ID
|| v_channel_id
|| v_SUBDEVICE_ID
|| v_RECVSUBDEV_ID
|| v_PROTOTYPE
|| v_PROTOCOLID
|| v_EVALTYPE
|| v_SEC
|| v_MSEC; SELECT COUNT ( * )
INTO m
FROM snmp
WHERE SUBSTR (uk_id, 1, 33) = temp_uk_id; IF m > 0
THEN
SELECT LPAD (m + 1, 4, '0') INTO v_m FROM DUAL;
ELSE
v_m := '0001';
END IF; new.uk_id := temp_uk_id || v_m;
EXCEPTION
WHEN OTHERS
THEN
v_errorcode := SQLCODE;
v_errortext := SUBSTR (SQLERRM, 1, 100); INSERT INTO ERROR_RECORD (ERROR_CODE,
error_msg,
error_date,
msg)
VALUES (v_errorcode,
v_errortext,
SYSDATE,
'tib_snmp ' || new.UK_ID);
END tib_snmp;
这个触发器不知道有什么问题?谢谢
CREATE OR REPLACE TRIGGER TIB_SNMP
BEFORE INSERT
ON SNMP
FOR EACH ROW
begin
declare n int;
declare temp_uk_id varchar(64);
declare v_uk_id varchar(64);
declare v_station_id varchar(4);
declare v_RECORD_ID varchar(4);
declare v_channel_id varchar(4);
declare v_SUBDEVICE_ID varchar(8);
declare v_RECVSUBDEV_ID varchar(8);
declare v_PROTOTYPE varchar(1);
declare v_PROTOCOLID varchar(2);
declare v_EVALTYPE varchar(2);
declare v_SEC varchar(10);
declare v_MSEC varchar(6);
declare m int;
declare v_m varchar(4);
declare v_errorcode int;
declare v_errortext varchar(200); SELECT LPAD (new.station_id, 4, '0') INTO v_station_id FROM DUAL;
SELECT LPAD (new.RECORD_ID, 4, '0') INTO v_RECORD_ID FROM DUAL;
SELECT LPAD (new.channel_id, 4, '0') INTO v_channel_id FROM DUAL;
SELECT LPAD (new.SENDSUBDEV_ID, 8, '0') INTO v_SUBDEVICE_ID FROM DUAL;
SELECT LPAD (new.RECVSUBDEV_ID, 8, '0') INTO v_RECVSUBDEV_ID FROM DUAL;
SELECT LPAD (new.PROTOTYPE, 1, '0') INTO v_PROTOTYPE FROM DUAL;
SELECT LPAD (new.PROTOCOLID, 2, '0') INTO v_PROTOCOLID FROM DUAL;
SELECT LPAD (new.EVALTYPE, 2, '0') INTO v_EVALTYPE FROM DUAL;
SELECT LPAD (new.SEC, 10, '0') INTO v_SEC FROM DUAL;
SELECT LPAD (new.MSEC, 6, '0') INTO v_MSEC FROM DUAL;
temp_uk_id :=
v_station_id
|| v_RECORD_ID
|| v_channel_id
|| v_SUBDEVICE_ID
|| v_RECVSUBDEV_ID
|| v_PROTOTYPE
|| v_PROTOCOLID
|| v_EVALTYPE
|| v_SEC
|| v_MSEC; SELECT COUNT ( * )
INTO m
FROM snmp
WHERE SUBSTR (uk_id, 1, 33) = temp_uk_id; IF m > 0
THEN
SELECT LPAD (m + 1, 4, '0') INTO v_m FROM DUAL;
ELSE
v_m := '0001';
END IF; new.uk_id := temp_uk_id || v_m;
EXCEPTION
WHEN OTHERS
THEN
v_errorcode := SQLCODE;
v_errortext := SUBSTR (SQLERRM, 1, 100); INSERT INTO ERROR_RECORD (ERROR_CODE,
error_msg,
error_date,
msg)
VALUES (v_errorcode,
v_errortext,
SYSDATE,
'tib_snmp ' || new.UK_ID);
END tib_snmp;
这个触发器不知道有什么问题?谢谢
ON `test`.`a`
FOR EACH ROW BEGIN
set new.d='123';
END;
$$DELIMITER ;
set new.uk_id = concat(temp_uk_id , v_m);
有LPAD函数
代码?
SELECT LPAD (new.RECORD_ID, 4, '0') INTO v_RECORD_ID FROM DUAL;
SELECT LPAD (new.channel_id, 4, '0') INTO v_channel_id FROM DUAL;
SELECT LPAD (new.SENDSUBDEV_ID, 8, '0') INTO v_SUBDEVICE_ID FROM DUAL;
SELECT LPAD (new.RECVSUBDEV_ID, 8, '0') INTO v_RECVSUBDEV_ID FROM DUAL;
SELECT LPAD (new.PROTOTYPE, 1, '0') INTO v_PROTOTYPE FROM DUAL;
SELECT LPAD (new.PROTOCOLID, 2, '0') INTO v_PROTOCOLID FROM DUAL;
SELECT LPAD (new.EVALTYPE, 2, '0') INTO v_EVALTYPE FROM DUAL;
SELECT LPAD (new.SEC, 10, '0') INTO v_SEC FROM DUAL;
SELECT LPAD (new.MSEC, 6, '0') INTO v_MSEC FROM DUAL;
注意NEW。字段是否有值
和 creat trigger 语句,这样可以直接模拟你的错误。
`UK_ID` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`PROTOTYPE` int(11) NULL DEFAULT NULL ,
`EVALTYPE` int(11) NULL DEFAULT NULL ,
`SNMP_VALUE` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`STATION_ID` int(11) NULL DEFAULT NULL ,
`RECORD_ID` int(11) NULL DEFAULT NULL ,
`CHANNEL_ID` int(11) NULL DEFAULT NULL ,
`SENDSUBDEV_ID` int(11) NULL DEFAULT NULL ,
`PROTOCOLID` int(11) NULL DEFAULT NULL ,
`PK_ID` int(11) NOT NULL AUTO_INCREMENT ,
`SEC` int(11) NULL DEFAULT NULL ,
`MSEC` int(11) NULL DEFAULT NULL ,
`RECVSUBDEV_ID` int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`PK_ID`),
UNIQUE INDEX `SNMP_PK` USING BTREE (`PK_ID`),
UNIQUE INDEX `NET_SNMP_U01` USING BTREE (`UK_ID`, `PROTOTYPE`, `EVALTYPE`, `SENDSUBDEV_ID`, `PROTOCOLID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
;CREATE DEFINER=`root`@`%` TRIGGER `tib_snmp` BEFORE INSERT ON `NewTable` FOR EACH ROW begin
declare n int;
declare temp_uk_id varchar(64);
declare v_uk_id varchar(64);
declare v_station_id varchar(4);
declare v_RECORD_ID varchar(4);
declare v_channel_id varchar(4);
declare v_SUBDEVICE_ID varchar(8);
declare v_RECVSUBDEV_ID varchar(8);
declare v_PROTOTYPE varchar(1);
declare v_PROTOCOLID varchar(2);
declare v_EVALTYPE varchar(2);
declare v_SEC varchar(10);
declare v_MSEC varchar(6);
declare m int;
declare v_m varchar(4) default '0001';
declare v_errorcode int;
declare v_errortext varchar(200); SELECT LPAD (new.station_id, 4, '0') INTO v_station_id FROM DUAL;
SELECT LPAD (new.RECORD_ID, 4, '0') INTO v_RECORD_ID FROM DUAL;
SELECT LPAD (new.channel_id, 4, '0') INTO v_channel_id FROM DUAL;
SELECT LPAD (new.SENDSUBDEV_ID, 8, '0') INTO v_SUBDEVICE_ID FROM DUAL;
SELECT LPAD (new.RECVSUBDEV_ID, 8, '0') INTO v_RECVSUBDEV_ID FROM DUAL;
SELECT LPAD (new.PROTOTYPE, 1, '0') INTO v_PROTOTYPE FROM DUAL;
SELECT LPAD (new.PROTOCOLID, 2, '0') INTO v_PROTOCOLID FROM DUAL;
SELECT LPAD (new.EVALTYPE, 2, '0') INTO v_EVALTYPE FROM DUAL;
SELECT LPAD (new.SEC, 10, '0') INTO v_SEC FROM DUAL;
SELECT LPAD (new.MSEC, 6, '0') INTO v_MSEC FROM DUAL;
select concat_ws('',v_station_id,v_RECORD_ID,v_channel_id,v_SUBDEVICE_ID,v_RECVSUBDEV_ID,v_PROTOTYPE,v_PROTOCOLID,v_EVALTYPE,v_SEC,v_MSEC) into temp_uk_id from dual;
SELECT COUNT (pk_id ) INTO m
FROM snmp
WHERE SUBSTR (uk_id, 1, 33) = temp_uk_id; IF m > 0
THEN
SELECT LPAD (m + 1, 4, '0') INTO v_m FROM DUAL;
END IF;select concat_ws('',temp_uk_id ,v_m) into v_uk_id from dual;
set new.uk_id=v_uk_id ;END;
这个表吗?
+-----------------+
| lpad(123,5,'@') |
+-----------------+
| @@123 |
+-----------------+
1 row in set
这个不是可以的么?
SELECT lpad(123,5,'@') ,lpad(null,5,'@') FROM DUAL
第二个个为NULL值,可以运行,怎么会报LPAD不存在?
-> values(11);
1305 - FUNCTION twar1.LPAD does not exist
楼上帮看看呢?
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> CREATE TABLE `NewTable` (
-> `UK_ID` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL
DEFAULT NULL ,
-> `PROTOTYPE` int(11) NULL DEFAULT NULL ,
-> `EVALTYPE` int(11) NULL DEFAULT NULL ,
-> `SNMP_VALUE` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci
NULL DEFAULT NULL ,
-> `STATION_ID` int(11) NULL DEFAULT NULL ,
-> `RECORD_ID` int(11) NULL DEFAULT NULL ,
-> `CHANNEL_ID` int(11) NULL DEFAULT NULL ,
-> `SENDSUBDEV_ID` int(11) NULL DEFAULT NULL ,
-> `PROTOCOLID` int(11) NULL DEFAULT NULL ,
-> `PK_ID` int(11) NOT NULL AUTO_INCREMENT ,
-> `SEC` int(11) NULL DEFAULT NULL ,
-> `MSEC` int(11) NULL DEFAULT NULL ,
-> `RECVSUBDEV_ID` int(11) NULL DEFAULT NULL ,
-> PRIMARY KEY (`PK_ID`),
-> UNIQUE INDEX `SNMP_PK` USING BTREE (`PK_ID`),
-> UNIQUE INDEX `NET_SNMP_U01` USING BTREE (`UK_ID`, `PROTOTYPE`, `EVALTYPE`
, `SENDSUBDEV_ID`, `PROTOCOLID`)
-> )
-> ENGINE=InnoDB
-> DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
-> AUTO_INCREMENT=1
-> ROW_FORMAT=COMPACT
-> ;
Query OK, 0 rows affected (0.08 sec)mysql> delimiter //
mysql>
mysql> CREATE TRIGGER `tib_snmp` BEFORE INSERT ON `NewTable` FOR EACH ROW begi
n
-> declare n int;
-> declare temp_uk_id varchar(64);
-> declare v_uk_id varchar(64);
-> declare v_station_id varchar(4);
-> declare v_RECORD_ID varchar(4);
-> declare v_channel_id varchar(4);
-> declare v_SUBDEVICE_ID varchar(8);
-> declare v_RECVSUBDEV_ID varchar(8);
-> declare v_PROTOTYPE varchar(1);
-> declare v_PROTOCOLID varchar(2);
-> declare v_EVALTYPE varchar(2);
-> declare v_SEC varchar(10);
-> declare v_MSEC varchar(6);
-> declare m int;
-> declare v_m varchar(4) default '0001';
-> declare v_errorcode int;
-> declare v_errortext varchar(200);
->
-> SELECT LPAD (new.station_id, 4, '0') INTO v_station_id FROM DUAL;
-> SELECT LPAD (new.RECORD_ID, 4, '0') INTO v_RECORD_ID FROM DUAL;
-> SELECT LPAD (new.channel_id, 4, '0') INTO v_channel_id FROM DUAL;
-> SELECT LPAD (new.SENDSUBDEV_ID, 8, '0') INTO v_SUBDEVICE_ID FROM DUAL; -> SELECT LPAD (new.RECVSUBDEV_ID, 8, '0') INTO v_RECVSUBDEV_ID FROM DUAL
;
-> SELECT LPAD (new.PROTOTYPE, 1, '0') INTO v_PROTOTYPE FROM DUAL;
-> SELECT LPAD (new.PROTOCOLID, 2, '0') INTO v_PROTOCOLID FROM DUAL;
-> SELECT LPAD (new.EVALTYPE, 2, '0') INTO v_EVALTYPE FROM DUAL;
-> SELECT LPAD (new.SEC, 10, '0') INTO v_SEC FROM DUAL;
-> SELECT LPAD (new.MSEC, 6, '0') INTO v_MSEC FROM DUAL;
->
-> select concat_ws('',v_station_id,v_RECORD_ID,v_channel_id,v_SUBDEVICE_ID,
v_RECVSUBDEV_ID,v_PROTOTYPE,v_PROTOCOLID,v_EVALTYPE,v_SEC,v_MSEC) into temp_uk_i
d from dual;
-> SELECT COUNT(pk_id ) INTO m
-> FROM snmp
-> WHERE SUBSTR(uk_id, 1, 33) = temp_uk_id;
->
-> IF m > 0
-> THEN
-> SELECT LPAD(m + 1, 4, '0') INTO v_m FROM DUAL;
-> END IF;
->
-> select concat_ws('',temp_uk_id ,v_m) into v_uk_id from dual;
-> set new.uk_id=v_uk_id ;
->
-> END;
->
-> //
Query OK, 0 rows affected (0.06 sec)mysql> delimiter ;
mysql> insert into NewTable (PK_ID) values (1);
Query OK, 1 row affected (0.05 sec)mysql>