这个是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
非常感谢,百分送上~~~
解决方案 »
- 有关pl/sql的一些问题
- 触发器的问题
- oralce字符集问题 急 大侠请进
- 关于在Oracle存储过程中返回合计行的问题
- 存储过程要返回结果集,并且有多个参数,有的参数的值是空值,是空值的不考虑,我该怎样写这个存储过程
- crontab 定时维护oracle的问题?
- oracle的存储过程编译的时候出现无反应,程序像死掉一样,
- 启动sqldba出现 00387---not toolkit resource file tk2cscoansi.res"
- 为什么我的oracle 8i 的OracleOraHomeManagementServer不能启动?
- OCILong 调用ORACLE9I数据库时出错(在线等待,立即散分)
- 为什么数据库自带的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;