这个是sqlserver的触发器CREATE TRIGGER InsertT_rule ON t_rule_ga
AFTER INSERT AS DECLARE @Prefix CHAR(2)
DECLARE @strMaxValue VARCHAR(10)
DECLARE @intMaxValue INT
DECLARE @ID VARCHAR(10) SET @Prefix = 'GA' --前缀
SET @intMaxValue = (SELECT f_rule_num_con FROM t_config_ga WHERE f_num_con = 1) --到配置表取当前应该插入的值
SET @strMaxValue = CAST(@intMaxValue AS VARCHAR(8))
SELECT @ID =
CASE LEN(@strMaxValue)
WHEN 1 THEN @Prefix + '0000000' + @strMaxValue --生成新ID
WHEN 2 THEN @Prefix + '000000' + @strMaxValue
WHEN 3 THEN @Prefix + '00000' + @strMaxValue
WHEN 4 THEN @Prefix + '0000' + @strMaxValue
WHEN 5 THEN @Prefix + '000' + @strMaxValue
WHEN 6 THEN @Prefix + '00' + @strMaxValue
WHEN 7 THEN @Prefix + '0' + @strMaxValue
WHEN 8 THEN @Prefix + @strMaxValue
END UPDATE t_rule_ga SET f_num_rul = @ID WHERE f_num_rul = '0000000000'
UPDATE t_config_ga SET f_rule_num_con = @intMaxValue + 1 WHERE f_num_con = 1
go
非常感谢,百分送上~~~
AFTER INSERT AS DECLARE @Prefix CHAR(2)
DECLARE @strMaxValue VARCHAR(10)
DECLARE @intMaxValue INT
DECLARE @ID VARCHAR(10) SET @Prefix = 'GA' --前缀
SET @intMaxValue = (SELECT f_rule_num_con FROM t_config_ga WHERE f_num_con = 1) --到配置表取当前应该插入的值
SET @strMaxValue = CAST(@intMaxValue AS VARCHAR(8))
SELECT @ID =
CASE LEN(@strMaxValue)
WHEN 1 THEN @Prefix + '0000000' + @strMaxValue --生成新ID
WHEN 2 THEN @Prefix + '000000' + @strMaxValue
WHEN 3 THEN @Prefix + '00000' + @strMaxValue
WHEN 4 THEN @Prefix + '0000' + @strMaxValue
WHEN 5 THEN @Prefix + '000' + @strMaxValue
WHEN 6 THEN @Prefix + '00' + @strMaxValue
WHEN 7 THEN @Prefix + '0' + @strMaxValue
WHEN 8 THEN @Prefix + @strMaxValue
END UPDATE t_rule_ga SET f_num_rul = @ID WHERE f_num_rul = '0000000000'
UPDATE t_config_ga SET f_rule_num_con = @intMaxValue + 1 WHERE f_num_con = 1
go
非常感谢,百分送上~~~
解决方案 »
- 跪求一个oracle时间区间查询的存储过程,具体如下?
- 我又来啦,存储过程中进行了增删改操作,我想在语句后手动添加日志,有哪些方法啊, 满分
- ORACLE-9i怎样实现增量备份
- select df,fdf,sdf, from tabe1 a,tale2 b where b.date>=sysdate-10 and a.date(+)>=sysdate-10
- Oracle Developer Tools for Visual Studio .NET 怎样才算规范安装?
- 救急!查询表空间使用情况的语句
- oracle 8.1.5中如何用exp备份
- 在linux下安装oracle的问题。
- 一个简单问题:如何设置时间的系统默认值为:yyyy-mm-dd hh:mm:ss这样的格式?
- ORACLE如何实现毫秒?
- 为什么数据库自带的utlsmtp.sql无法执行 报ora-00900错误
- 远程连接Oracle数据库问题????在线等
-------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER InsertT_rule ON t_rule_ga
AFTER INSERT
IS
BEGIN
UPDATE t_rule_ga
SET
f_num_rul=(SELECT 'GA'||SUBSTR('0000000'||TO_CHAR(f_rule_num_con),LENGTH(TO_CHAR(f_rule_num_con)),8) FROM t_config_ga WHERE f_num_con=1)
WHERE
f_num_rul='0000000000';
UPDATE t_config_ga SET f_rule_num_con=f_rule_num_con+1 WHERE f_num_con=1;
END;
AFTER INSERT AS Prefix CHAR(2);
strMaxValue VARCHAR(10);
intMaxValue INT;
ID VARCHAR(10);
bnegin Prefix := 'GA' ; --前缀
SELECT f_rule_num_con into intMaxValue FROM t_config_ga WHERE f_num_con = 1;
ID :=lpad(strMaxValue,8,'0');
UPDATE t_rule_ga SET f_num_rul = ID WHERE f_num_rul = '0000000000';
UPDATE t_config_ga SET f_rule_num_con = intMaxValue + 1 WHERE f_num_con = 1;
END;