希望下列代码能够给初学sql的朋友提供帮助
1表一：
CREATE TABLE [dbo].[A_Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NULL,
[mystate] [nchar](10) NULL
) ON [PRIMARY]
GO
2表二：
CREATE TABLE [dbo].[A_Table_2](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NULL,
[sub_state] [nchar](10) NULL,
[main_state] [nchar](10) NULL
) ON [PRIMARY]
GO
3插入测试脚本：
INSERT INTO [dbo].[A_Table_1]
([name]
,[mystate])
VALUES
('P'
,'0')
GO
4触发器实现前后两次录入数据是否1~0还是0~1变化，并将故障情况写入库
注意：派生表必须要有别名！
declare @state1 char(10)
set @state1 ='发生故障'
declare @state2 char(10)
set @state2 = '已解决故障'
declare @name char(50)
declare @state char(1)
--最新第一条数据
select top 1
@name = name,
@state = mystate
from [GQ].[dbo].[A_Table_1] ORDER BY id DESC
--最新第二条数据
declare @second_state char(1)
select top 1 @second_state = mytab.mystate from (select top 2 * from [GQ].[dbo].[A_Table_1] order by id desc) as mytab order by mytab.mystate asc
if (@state='1' and @second_state<>'1') INSERT INTO [dbo].[A_Table_2] VALUES (@name,@state,@state1)
if (@state='0' and @second_state<>'1') INSERT INTO [dbo].[A_Table_2] VALUES (@name,@state,@state2)
end
