Public Function tianshu(ByVal a As String) As Integer Dim iyear, imonth, iday As Integer iyear = Year(a) imonth = Month(a) If (iyear Mod 4 = 0) And (iyear Mod 100 = 0) Then iday = 1 ElseIf (iyear Mod 4 = 0) And (iyear Mod 100 <> 0) Then iday = 1 Else iday = 0 End If Select Case imonth Case 1, 3, 5, 7, 8, 10, 12 Return 31 Case 4, 6, 9, 11 Return 30 Case 2 If iday = 1 Then Return 29 Else Return 28 End If End Select End Function
CREATE TABLE T (id INT IDENTITY(1,1) ,Num VARCHAR(18),birthDay VARCHAR(8)) CREATE TRIGGER trg ON T INSTEAD OF INSERT AS DECLARE @Id_Num VARCHAR(18) DECLARE @Id INT DECLARE @BirthDay VARCHAR(8) SELECT @Id_Num=Num FROM INSERTED IF LEN(@Id_Num)=18 SET @BirthDay=SUBSTRING(@Id_Num,7,8) ELSE BEGIN SET @BirthDay=SUBSTRING(@Id_Num,7,6) END INSERT INTO T SELECT @Id_Num,@BirthDay
修改上面的,上面的触发器基本理解有问题,只能解决单条数据录入问题,是个错误的触发器写法 CREATE TABLE Table_Pqs(id INT IDENTITY(1,1) ,Num VARCHAR(18),sfz varchar(18),birthDay VARCHAR(8)) go CREATE TRIGGER trg ON Table_Pqs for INSERT AS update Table_Pqs SET birthDay=case when len(i.sfz)=18 then SUBSTRING(i.sfz,7,8) when len(i.sfz)=15 then '19'+SUBSTRING(i.sfz,7,6) else '0' end from inserted i where i.id=Table_Pqs.idgo
Dim iyear, imonth, iday As Integer
iyear = Year(a)
imonth = Month(a)
If (iyear Mod 4 = 0) And (iyear Mod 100 = 0) Then iday = 1
ElseIf (iyear Mod 4 = 0) And (iyear Mod 100 <> 0) Then
iday = 1
Else
iday = 0
End If
Select Case imonth
Case 1, 3, 5, 7, 8, 10, 12
Return 31
Case 4, 6, 9, 11
Return 30
Case 2
If iday = 1 Then
Return 29
Else
Return 28
End If
End Select
End Function
CREATE TABLE T (id INT IDENTITY(1,1) ,Num VARCHAR(18),birthDay VARCHAR(8))
CREATE TRIGGER trg
ON T INSTEAD OF INSERT
AS
DECLARE @Id_Num VARCHAR(18)
DECLARE @Id INT
DECLARE @BirthDay VARCHAR(8)
SELECT @Id_Num=Num FROM INSERTED
IF LEN(@Id_Num)=18
SET @BirthDay=SUBSTRING(@Id_Num,7,8)
ELSE
BEGIN
SET @BirthDay=SUBSTRING(@Id_Num,7,6)
END
INSERT INTO T SELECT @Id_Num,@BirthDay
CREATE TABLE Table_Pqs(id INT IDENTITY(1,1) ,Num VARCHAR(18),sfz varchar(18),birthDay VARCHAR(8))
go
CREATE TRIGGER trg
ON Table_Pqs for INSERT
AS
update Table_Pqs
SET birthDay=case when len(i.sfz)=18 then SUBSTRING(i.sfz,7,8)
when len(i.sfz)=15 then '19'+SUBSTRING(i.sfz,7,6)
else '0' end
from inserted i where i.id=Table_Pqs.idgo