34,590
社区成员
发帖
与我相关
我的任务
分享
--DROP TRIGGER YHJ_GYH_GB_CODE_INSERT
CREATE TRIGGER [dbo].YHJ_GYH_GB_CODE_INSERT ON [dbo].YHJ_GYH_GB_CODE
FOR INSERT
AS
BEGIN
INSERT INTO YHJ_GYH_GB_CODE
( A1 ,
A2 ,
A3
)
SELECT inserted.A1 ,
inserted.A2 ,
CONVERT(VARCHAR(100), GETDATE(), 21)
FROM inserted;
END
用这个,就OK了。感谢各位:
CREATE TRIGGER [dbo].YHJ_GYH_GB_CODE_TEST_INSERT ON [dbo].YHJ_GYH_GB_CODE_TEST
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO YHJ_GYH_GB_CODE_TEST
( A1 ,
A2 ,
A3
)
SELECT inserted.A1 ,
inserted.A2 ,
CONVERT(VARCHAR(100), GETDATE(), 21)
FROM inserted;
END
1、改成用instead of 的触发器,
2、用约束。也是好办法。虽然我没测试。create table YHJ_GYH_GB_CODE(A1 int, A2 int, A3 varchar(30))
go
CREATE TRIGGER [dbo].YHJ_GYH_GB_CODE_INSERT ON [dbo].YHJ_GYH_GB_CODE
instead of insert
AS
BEGIN
INSERT INTO YHJ_GYH_GB_CODE
( A1 ,
A2 ,
A3
)
SELECT inserted.A1 ,
inserted.A2 ,
CONVERT(VARCHAR(100), GETDATE(), 21)
FROM inserted;
END
go
insert into YHJ_GYH_GB_CODE(A1,A2) values(11,111)
go
insert into YHJ_GYH_GB_CODE(A1,A2,A3) values(22,222,'2011-01-01')
go
select * from YHJ_GYH_GB_CODE
go
drop table YHJ_GYH_GB_CODE
go
结果是:
11 111 2018-12-05 20:35:05.110
22 222 2018-12-05 20:35:05.123
-- 楼主,把你的测试结果发上来。
--DROP TRIGGER YHJ_GYH_GB_CODE_INSERT
create table YHJ_GYH_GB_CODE(A1 int, A2 int, A3 varchar(30))
go
CREATE TRIGGER [dbo].YHJ_GYH_GB_CODE_INSERT ON [dbo].YHJ_GYH_GB_CODE
FOR INSERT
AS
BEGIN
INSERT INTO YHJ_GYH_GB_CODE
( A1 ,
A2 ,
A3
)
SELECT inserted.A1 ,
inserted.A2 ,
CONVERT(VARCHAR(100), GETDATE(), 21)
FROM inserted;
END
go
insert into YHJ_GYH_GB_CODE(A1,A2) values(11,111)
go
insert into YHJ_GYH_GB_CODE(A1,A2,A3) values(22,222,'2011-01-01')
go
select * from YHJ_GYH_GB_CODE
go
drop table YHJ_GYH_GB_CODE
go
A1 A2 A3
----------- ----------- ------------------------------
11 111 NULL
11 111 2018-12-05 08:22:49.467
22 222 2011-01-01
22 222 2018-12-05 08:22:49.473
(4 行受影响)
ALTER TABLE YHJ_GYH_GB_CODE ADD CONSTRAINT DF_YHJ_GYH_GB_CODE_A3 DEFAULT GETDATE() FOR A3;
加约束之后, 你在执行
INSERT INTO YHJ_GYH_GB_CODE ( A1 , A2 ) VALUES( @A1, @A2 )
语句时, A3 会自动赋默认值 , 不需要你操心的。