| 導購 | 订阅 | 在线投稿
分享
 
 
 

兩個sqlserver存儲過程,一個觸發器

來源:互聯網  2008-05-18 23:55:16  評論

1. 備份日志, 避免日志過快增長 no_log / trancate_only

BACKUP LOG realnew_DATA WITH NO_LOG

DBCC SHRINKDATABASE(realnew_DATA ,TRUNCATEONLY)

BACKUP LOG realnew_DATA WITH Truncate_ONLY

BACKUP LOG sjyh WITH NO_LOG

DBCC SHRINKDATABASE(sjyh,TRUNCATEONLY)

BACKUP LOG sjyh WITH Truncate_ONLY

2. 維持曆史庫數據表中的記錄不超過5000條, 並將實時庫中數據插入曆史庫

1) 使用存儲過程 --作業

CREATE procedure insert_calHistory as

if ((select count(tag_id) from CalcOUT_1_HIS)>3000)

begin

delete from CalcOUT_1_HIS where tag_id in (select top 500 tag_id from CalcOUT_1_HIS order by tag_id)

end

insert into CalcOUT_1_HIS select * from CalcOUT_1_REAL

go

然後,添加作業

調度:每天每隔3分鍾執行一次

SQL: exec insert_calHistory

2) 使用觸發器--針對表一級的

CREATE TRIGGER deleterecord ON [dbo].[testtrigger]

after INSERT

AS

IF

(SELECT COUNT(*) FROM testtrigger) >5000

BEGIN

DELETE FROM testtrigger where id not in ( select top 300 id from testtrigger order by id desc)

END

  1. 備份日志, 避免日志過快增長 no_log / trancate_only   BACKUP LOG realnew_DATA WITH NO_LOG   DBCC SHRINKDATABASE(realnew_DATA ,TRUNCATEONLY)   BACKUP LOG realnew_DATA WITH Truncate_ONLY   BACKUP LOG sjyh WITH NO_LOG   DBCC SHRINKDATABASE(sjyh,TRUNCATEONLY)   BACKUP LOG sjyh WITH Truncate_ONLY   2. 維持曆史庫數據表中的記錄不超過5000條, 並將實時庫中數據插入曆史庫    1) 使用存儲過程 --作業   CREATE procedure insert_calHistory as   if ((select count(tag_id) from CalcOUT_1_HIS)>3000)    begin    delete from CalcOUT_1_HIS where tag_id in (select top 500 tag_id from CalcOUT_1_HIS order by tag_id)    end   insert into CalcOUT_1_HIS select * from CalcOUT_1_REAL   go   然後,添加作業    調度:每天每隔3分鍾執行一次    SQL: exec insert_calHistory   2) 使用觸發器--針對表一級的   CREATE TRIGGER deleterecord ON [dbo].[testtrigger]   after INSERT   AS   IF   (SELECT COUNT(*) FROM testtrigger) >5000   BEGIN    DELETE FROM testtrigger where id not in ( select top 300 id from testtrigger order by id desc)   END
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有