sql server trigger

sql server 建立 trigger例子。

当testresults表数据有插入或更新的时候，对TestResultsPnR表做同步记录操作。

 

CREATE TRIGGER dbo.PNRINSERT ON dbo.TestResults
FOR insert
AS
DECLARE
@vConfigId   INT,
@vTestset VARCHAR(100),
@vComponent char(50),
@vUsers smallint,
@vIterations int,
@vKeyword char(50),
@vStat_Calculation char(25),
@vStat_Value float(53),
@insertdate datetime,
@updatedate datetime;

SELECT @vConfigId = configid, @vTestset = testset ,@vComponent=component ,@vUsers=users,@vIterations=iterations,@vKeyword=keyword,@vStat_calculation=stat_calculation,@vUsers=users,@vIterations=iterations,@insertdate = getdate(),@vStat_Value = Stat_Value FROM INSERTED;

--execute after src table update/insert commit
delete from TestResultsPnR where configid = @vConfigId andtestset=@vTestsetandcomponent=@vComponentandusers=@vUsersanditerations=@vIterationsandkeyword=@vKeywordandstat_calculation=@vStat_calculation;

insert into TestResultsPnR (configid,testset,component,users,iterations,keyword,stat_calculation,stat_value,insertdate) values(@vConfigId,@vTestset,@vComponent,@vUsers,@vIterations,@vKeyword,@vStat_calculation,@vStat_value,@insertdate);