ALTER trigger [dbo].[insert_IdleHours]
on [dbo].[IdleHours]
after INSERT
as
BEGIN
declare
@AutoNo numeric(18, 0) ,
@mAutono Varchar(18),
@TransactionDate datetime,
@mtable varchar(200),
@mcount int,
@mInsert varchar(200)
Select @AutoNo = AutoNo from inserted;
Select @TransactionDate = TransactionDate from inserted;
Select @mtable = 'IdleHours'+convert(varchar(4),YEAR(@TransactionDate))
Set @mAutono = Cast(@AutoNo as Varchar)
Select @mcount = count(*) from YearDataInfo where yeardata = convert(varchar(4),YEAR(@TransactionDate))
If @mcount = 0
Begin
Set @mtable = rtrim(ltrim('IdleHours'+convert(char(4),YEAR(@TransactionDate))))
if not exists(select 0 from sysobjects where name=@mtable and xtype='U')
Begin
Set @mtable = 'SELECT * INTO ' + @mtable + ' FROM IdleHours2023 WHERE 1=2'
Exec (@mtable)
Insert Into YearDataInfo(YearData) values(convert(varchar(4),YEAR(@TransactionDate)))
End
End
Select @mInsert = 'Insert Into ' + @mtable + ' Select * from IdleHours where AutoNo = ' + @mAutoNo
Exec(@mInsert)
END
SQL触发器按年分表存