Thought of Genius

備份結尾交易並執行資料庫還原

0 意見張貼者： Thought 於晚上8:59

當資料庫發生異常的時候，通常都希望能在很短的時間內還原資料庫，每當問題發生時，再撰寫資料庫還原的T-SQL，往往耽誤了許多時間。所以我們可以事先撰寫還原的T-SQL，以備不時之需。
假設我們的備份策略為：
每天凌晨 AM 3:00 處理一次完整備份，每3個小時處理一次差異備份，每1個小時處理一次交易備份。

還原策略說明：
1. 備份結尾交易紀錄，並設定資料庫為還原模式。
2. 建立暫存資料表。
3. 檢查備份檔案相關資訊，並寫入暫存資料表。
4. 還原完整備份。
5. 還原最後一個差異備份。(如果沒有差異備份，則此步驟會跳過。)
6. 依順序還原最後一個差異備份之後所有的交易備份。(如果沒有差異備份，依順序還原完整備份之後所有交易備份。)
7. 還原結尾交易備份至特定時間點。

 USE master -- Variable announcement DECLARE @DBName nvarchar(32) DECLARE @DirectoryPath nvarchar(128) DECLARE @FileName nvarchar(32) DECLARE @FilePath nvarchar(128) DECLARE @TailPath nvarchar(128) DECLARE @StopTime datetime -- SET @DBName, @DirectoryPath, @FileName, @StopTime SET @DBName = 'Lab' SET @DirectoryPath = 'C:' SET @FileName = 'Lab.bak' SET @StopTime = '2008-11-03 10:38:19' SET @FilePath = @DirectoryPath + '\' + @FileName SET @TailPath = @DirectoryPath + '\' + @DBName + '_TailLog_' + CONVERT(varchar, GETDATE(), 112 ) +'_'+ REPLACE (CONVERT(varchar, GETDATE(), 108 ),':','') + '.bak' -- Backup TailLog BACKUP LOG @DBName TO DISK = @TailPath WITH NAME = @DBName, DESCRIPTION = 'TailLog', NOFORMAT, INIT, NORECOVERY -- Create Temporary Table -- SQL Server 2000, 2005, 2008 column quantity is different CREATE TABLE #T ( BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime NULL, Compressed tinyint, Position smallint, DeviceType tinyint NULL, UserName nvarchar(128), ServerName nvarchar(128) NULL, DatabaseName nvarchar(128) NULL, DatabaseVersion int NULL, DatabaseCreationDate datetime NULL, BackupSize numeric(20,0) NULL, FirstLSN numeric(25,0) NULL, LastLSN numeric(25,0) NULL, CheckpointLSN numeric(25,0) NULL, DatabaseBackupLSN numeric(25,0) NULL, BackupStartDate datetime, BackupFinishDate datetime, SortOrder smallint NULL, CodePage smallint NULL, UnicodeLocaleId int NULL, UnicodeComparisonStyle int NULL, CompatibilityLevel tinyint NULL, SoftwareVendorId int, SoftwareVersionMajor int, SoftwareVersionMinor int, SoftwareVersionBuild int NULL, MachineName nvarchar(128), Flags int NULL, BindingID uniqueidentifier NULL, RecoveryForkID uniqueidentifier NULL, Collation nvarchar(128) NULL, FamilyGUID uniqueidentifier NULL, HasBulkLoggedData bit NULL, IsSnapshot bit NULL, IsReadOnly bit NULL, IsSingleUser bit NULL, HasBackupChecksums bit NULL, IsDamaged bit NULL, BeginsLogChain bit NULL, HasIncompleteMetaData bit NULL, IsForceOffline bit NULL, IsCopyOnly bit NULL, FirstRecoveryForkID uniqueidentifier NULL, ForkPointLSN numeric(25,0) NULL, RecoveryModel nvarchar(60) NULL, DifferentialBaseLSN numeric(25,0) NULL, DifferentialBaseGUID uniqueidentifier NULL, BackupTypeDescription nvarchar(60), BackupSetGUID uniqueidentifier NULL, CompressedBackupSize int NULL ) -- Check Backup SET and Insert INTO Temporary Table INSERT #T EXEC('RESTORE HEADERONLY FROM DISK = '''+ @FilePath +'''') -- Restore Full Backup RESTORE DATABASE @DBName FROM DISK = @FilePath WITH NORECOVERY,FILE = 1 -- Restore Last Differential Backup DECLARE @PtnD smallint DECLARE @CheckD bit SET @CheckD = 0 DECLARE CurD CURSOR FOR SELECT TOP(1) Position FROM #T WHERE BackupType = 5 ORDER BY BackupStartDate DESC, Position DESC OPEN CurD FETCH NEXT FROM CurD INTO @PtnD WHILE(@@FETCH_STATUS=0) BEGIN RESTORE DATABASE @DBName FROM DISK = @FilePath WITH NORECOVERY,FILE = @PtnD SET @CheckD = 1 FETCH NEXT FROM CurD INTO @PtnD END CLOSE CurD DEALLOCATE CurD -- Restore Log Backup From Last Differential Backup or From Full Backup DECLARE @PtnL smallint IF @CheckD = 1 BEGIN DECLARE CurL CURSOR FOR SELECT Position FROM #T WHERE Position > (SELECT TOP(1) Position FROM #T WHERE BackupType = 5 ORDER BY BackupStartDate DESC, Position DESC) OPEN CurL FETCH NEXT FROM CurL INTO @PtnL WHILE(@@FETCH_STATUS=0) BEGIN RESTORE LOG @DBName FROM DISK = @FilePath WITH NORECOVERY,FILE = @PtnL FETCH NEXT FROM CurL INTO @PtnL END CLOSE CurL DEALLOCATE CurL END ELSE BEGIN DECLARE CurL CURSOR FOR SELECT Position FROM #T WHERE Position > 1 OPEN CurL FETCH NEXT FROM CurL INTO @PtnL WHILE(@@FETCH_STATUS=0) BEGIN RESTORE LOG @DBName FROM DISK = @FilePath WITH NORECOVERY,FILE = @PtnL FETCH NEXT FROM CurL INTO @PtnL End CLOSE CurL DEALLOCATE CurL End -- Restore TailLog Backup Stop at Specific Time RESTORE LOG @DBName FROM DISK = @TailPath WITH RECOVERY, STOPAT = @StopTime, FILE = 1 GO

SQL Server 備份(Backup)與還原(Restore)說明

0 意見張貼者： Thought 於上午10:42

復原模式(Recovery Model) :
發生檢查點(check point)之後，是否繼續存留交易紀錄在資料庫的交易紀錄檔之中。

完整備份(Full Backup) : Data + Uncommitted Log
將整個資落庫的資料與部分交易。在作業的過程中任何的作業完成與未完成的交易都會進行備份。

差異備份(Differential Backup) :
1.從最近一次的完整備份，到現在的資料量變化的備份作業。
2.僅能將資料庫還原至差異備份的時間點。
3.最近一次的差異備份可以取代前一次的差異備份。

交易紀錄備份(Log Backup) : Backup Log + Clear Log
1.進行交易備份之後，系統會自動截斷不在使用的交易紀錄檔，並釋放空間。
2.每一份交易備份的備份資料，是屬於接續性的關係。
3.可以將系統狀態還原至特定的時間點。

結尾交易備份(Tail-log Backup) :
當系統要進行還原時，第一個首要工作就是，將剩餘的交易紀錄檔進行備份。

 -- Create database USE master GO IF EXISTS (SELECT name FROM sys.databases WHERE name = 'Lab') DROP DATABASE Lab GO CREATE DATABASE [Lab] ON PRIMARY ( NAME = N'Lab_DATA', FILENAME = N'C:\Lab_DATA.MDF' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), FILEGROUP [Lab_FG01] ( NAME = N'Lab_FG01', FILENAME = N'C:\Lab_FG01.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), FILEGROUP [Lab_FG02] ( NAME = N'Lab_FG02', FILENAME = N'C:\Lab_FG02.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), FILEGROUP [Lab_FG03] ( NAME = N'Lab_FG03', FILENAME = N'C:\Lab_FG03.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON ( NAME = N'Lab_LOG', FILENAME = N'C:\Lab_LOG.LDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%) GO -- Full Backup USE master GO BACKUP DATABASE Lab TO DISK='C:\Lab.bak' WITH NAME = N'Lab', DESCRIPTION = N'Full_Backup', NOFORMAT, INIT GO -- Differential Backup USE master GO BACKUP DATABASE Lab TO DISK = N'C:\Lab.bak' WITH NAME = N'Lab', DESCRIPTION = N'Differential_Bckup', DIFFERENTIAL, NOFORMAT, NOINIT GO -- Log Backup USE master GO BACKUP LOG Lab TO DISK = N'C:\Lab.bak' WITH NAME = N'Lab', DESCRIPTION = N'Log_Backup', NOFORMAT, NOINIT GO --Tail Log Backup USE master GO BACKUP LOG Lab TO DISK = N'C:\Lab.bak' WITH NAME = N'Lab', DESCRIPTION = N'Tail_Log_Backup', NORECOVERY GO -- View and check backup set information RESTORE HEADERONLY FROM DISK = N'C:\Lab.bak' RESTORE FILELISTONLY FROM DISK = N'C:\Lab.bak' -- Restore database according to column position and move datafile USE master GO RESTORE DATABASE Lab FROM DISK = N'C:\Lab.bak' WITH NORECOVERY, FILE = 1, MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF', MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF', MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF', MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF' GO RESTORE DATABASE Lab FROM DISK = N'C:\Lab.bak' WITH NORECOVERY, FILE = 2, MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF', MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF', MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF', MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF' GO RESTORE LOG Lab FROM DISK = N'C:\Lab.bak' WITH NORECOVERY, FILE = 3, MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF', MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF', MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF', MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF' GO RESTORE LOG Lab FROM DISK = N'C:\Lab.bak' WITH RECOVERY, FILE = 4, MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF', MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF', MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF', MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF' GO -- Drop database DROP DATABASE Lab -- Execute cmd.exe DEL C:\Lab.bak

使用 Store Procedure 發送電子郵件

0 意見張貼者： Thought 於下午1:13

SQL Server 2000 的 SQL Mail 提供從 Microsoft SQL Server 傳送和讀取電子郵件的簡易方式，但是 SQL Mail 屬於 MAPI 應用程式，所以伺服器上必須出現 MAPI 子系統。如果要使用 SQL Mail，就必須安裝 MAPI 用戶端，例如：Microsoft Outlook 或是 Exchange Server。本文件所提及之技術，伺服器可以不必安裝MAPI 子系統，即可發送電子郵件。

我們可以利用 SQL Server OLE Automation 系統預存程序，來呼叫 CDOSYS 物件模型，並搭配任何支援 SMTP 郵件的郵件伺服器，作為自己的遠端 SMTP 郵件伺服器，來達到寄送電子郵件的功能。

 -- If this store procedure exists, drop this store procedure IF (EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_cdosys_sendmail')) DROP PROCEDURE usp_cdosys_sendmail GO -- Create store procedure CREATE PROCEDURE usp_cdosys_sendmail @From varchar(100) , @To varchar(100) , @Subject varchar(100)=" ", @Body varchar(4000) =" " AS DECLARE @iMsg int DECLARE @SMTPServer varchar(100) DECLARE @SendUserName varchar(100) DECLARE @SendPassword varchar(100) DECLARE @hr int DECLARE @source varchar(255) DECLARE @description varchar(500) DECLARE @output varchar(1000) -- This is a sample. Please input your smtp server name or IP address SET @SMTPServer='mail.com.tw' -- If smtp server uses authentication, please input your email address and email password. If does not, pass this option. SET @SendUserName='admin@mail.com.tw ' SET @SendPassword='Password' -- Create the CDO.Message Object EXEC sp_OACreate 'CDO.Message', @iMsg OUT -- Configure a remote SMTP server. EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' -- Configure the Server Name or IP address. EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',@SMTPServer -- SMTP Authentication IF NOT (@SendUserName='' and @SendPassword='') BEGIN EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1' EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@SendUserName EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@SendPassword END -- Save the configurations to the message object EXEC sp_OAMethod @iMsg, 'Configuration.Fields.Update', null -- Set the e-mail parameters EXEC sp_OASetProperty @iMsg, 'From', @From EXEC sp_OASetProperty @iMsg, 'To', @To EXEC sp_OASetProperty @iMsg, 'Subject', @Subject EXEC sp_OASetProperty @iMsg, 'TextBody', @Body -- Send mail and do error handling EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL IF @hr <> 0 BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Description: ' + @description PRINT @output END END -- Clean up the objects created. EXEC sp_OADestroy @iMsg GO -- Execute procedure DECLARE @Body varchar(4000) SELECT @Body = 'Your application is rejected. Please contact the system manager.' EXEC usp_cdosys_sendmail 'admin@mail.com.tw','ada@mail.com.tw','System Message',@Body

本機交易(Supporting Local Transactions)說明

0 意見張貼者： Thought 於晚上9:27

本機交易通常使用在主、從式應用程式中，更新的對象限定於同一個資料來源。應用程式可以使用在ADO.NET當中的Connection物件搭配Transaction物件來完成，而Command物件有一個Transaction屬性，允許把想要執行的多個命令，包裝成一個單獨的交易。讓整個交易中所有的工作，都有一致的結果，不是完全成功執行就是完全失敗。

 <connectionStrings> <add name="DataAccess.Properties.Settings.NorthwindConStr" connectionString="Data Source=localhost;Initial Catalog=Northwind;User ID=sa; Password=P@$sW0rD; MultipleActiveResultSets=True" providerName="System.Data.SqlClient" /> </connectionStrings> public class Trans { private SqlConnection m_Connection = new SqlConnection(Properties.Settings.Default.NorthwindConStr); private string _ErrorStr; public string ErrorStr { get { return this._ErrorStr; } set { this._ErrorStr = value; } } public bool Transact() { bool result = false; SqlTransaction tx = null; string cmdstr1 = @"UPDATE dbo.Products SET UnitPrice = UnitPrice*1.2"; string cmdstr2 = @"UPDATE Production.Product SET ListPrice = ListPrice*1.2"; try { m_Connection.Open(); tx = m_Connection.BeginTransaction(); SqlCommand cmd1 = new SqlCommand(cmdstr1, m_Connection, tx); SqlCommand cmd2 = new SqlCommand(cmdstr2, m_Connection, tx); cmd1.ExecuteNonQuery(); m_Connection.ChangeDatabase("AdventureWorks"); cmd2.ExecuteNonQuery(); tx.Commit(); result = true; } catch (SqlException sqlex) { this.ErrorStr = sqlex.Message.ToString(); tx.Rollback(); } finally { m_Connection.Close(); } return result; } }

MARS多重查詢結果集(Multiple Active Result Sets)

0 意見張貼者： Thought 於晚上9:25

以往在同一個Connection當中，分別以多個DataReader搭配Command物件，或者是透過DataAdapter讀取資料時，一次只有一個命令可以執行查詢，查詢的動作也會循序地執行。我們無法一邊讀取資料，一邊利用相同的Connection，來進行其他資料的查詢、新增、修改、刪除等等動作。
現在我們可以利用SQL Server 2005所提供的功能MARS (Multiple Active Result Sets)，它允許在單一資料庫連線上執行多的批次作業，並同時操作多個查詢結果集，或同時進行資料的新增、修改、刪除作業。例如，當應用程式開啟一個DataReader而且尚未關閉的情況下，我們可以利用相同的連線，送出另一個查詢命令到資料來源。
預設MARS功能是停用的，必須在連線字串中，增加一組鍵值方可啟用。

 <connectionStrings> <add name="DataAccess.Properties.Settings.NorthwindConStr" connectionString="Data Source=localhost;Initial Catalog=Northwind;User ID=sa; Password=P@$sW0rD; MultipleActiveResultSets=True" providerName="System.Data.SqlClient" /> </connectionStrings> public class MARS { private SqlConnection m_Connection = new SqlConnection(Properties.Settings.Default.NorthwindConStr); private string _ErrorStr; public string ErrorStr { get { return this._ErrorStr; } set { this._ErrorStr = value; } } public bool ExecuteMars() { bool result = false; string cmdstr1 = @"SELECT ProductID, UnitPrice FROM Products"; string cmdstr2 = @"UPDATE Products SET UnitPrice = @UnitPrice WHERE ProductID = @ProductID"; SqlCommand cmd1 = new SqlCommand(cmdstr1, m_Connection); SqlCommand cmd2 = new SqlCommand(cmdstr2, m_Connection); cmd2.Parameters.Add("@ProductID", SqlDbType.Int); cmd2.Parameters.Add("@UnitPrice", SqlDbType.Money); try { m_Connection.Open(); SqlDataReader dr = cmd1.ExecuteReader(); while (dr.Read()) { cmd2.Parameters[0].Value = Convert.ToInt32(dr[0]); cmd2.Parameters[1].Value = Convert.ToDouble(dr[1])*1.2; cmd2.ExecuteNonQuery(); } result = true; } catch (Exception ex) { this.ErrorStr = ex.Message.ToString(); } finally { m_Connection.Close(); } return result; } }

使用DML Trigger稽核紀錄資料表異動

0 意見張貼者： Thought 於晚上9:41

Oracle資料庫的DML觸發程式是由DML敘述所激發，而敘述的類型決定了DML觸發程式的類型。它可以針對INSERT、UPDATE、DELETE操作定義，也可以在所有的資料列或特定的資料列起作用。敘述層次的觸發程式使用WHEN子句，來評定是否有發生特定類型的改變。在觸發程式內，你可以使用 :old 或 :new的繫結變數，來存取目前被處理的資料。PL/SQL編譯器會把它當成由類型組成的紀錄： triggering_table%ROWTYPE ，定義此觸發程式的表格。使用方式為 :old.field 或 :new.field。它也提供三個布林函數可以判定操作類型，分別為INSERTING、UPDATING、DELETING，當敘述符合時，它就是TRUE；否則就是FALSE。

 -- Create actual tble and trace table CREATE TABLE REPORT ( REPORTNO NUMBER(10) NOT NULL, REGDATE DATE NOT NULL, NOTE VARCHAR2(1024) ); CREATE TABLE REPORT_TRACE ( ACTION VARCHAR2(6) NOT NULL, ACTIONTIME DATE NOT NULL, REPORTNO NUMBER(10) NOT NULL, REGDATE DATE NOT NULL, NOTE VARCHAR2(1024) ); -- Create trigger to audit CREATE OR REPLACE TRIGGER TR_REPORT AFTER INSERT OR UPDATE OR DELETE ON REPORT FOR EACH ROW WHEN (new.REPORTNO > 1000 OR old.REPORTNO > 1000) BEGIN IF INSERTING THEN INSERT INTO REPORT_TRACE VALUES('INSERT', SYSDATE, :new.REPORTNO, :new.REGDATE, :new.NOTE); ELSIF UPDATING THEN INSERT INTO REPORT_TRACE VALUES('UPDATE', SYSDATE, :new.REPORTNO, :new.REGDATE, :new.NOTE); ELSIF DELETING THEN INSERT INTO REPORT_TRACE VALUES('DELETE', SYSDATE, :old.REPORTNO, :old.REGDATE, :old.NOTE); END IF; END; / -- Input data INSERT INTO REPORT VALUES(100, sysdate, 'frist record'); INSERT INTO REPORT VALUES(1501, sysdate, 'second record'); UPDATE REPORT SET NOTE = 'frist record update' WHERE REPORTNO = 100; UPDATE REPORT SET NOTE = 'second record update' WHERE REPORTNO = 1501; DELETE REPORT WHERE REPORTNO IN (100, 1501); COMMIT; -- Query data SELECT * FROM REPORT; SELECT * FROM REPORT_TRACE; -- Drop table DROP TABLE REPORT CASCADE CONSTRAINTS; DROP TABLE REPORT_TRACE CASCADE CONSTRAINTS;

使用 SQL Server DML Trigger 處理交易的延伸

0 意見張貼者： Thought 於晚上8:59

假設現在我們有4個需求分別是：
1. 新增訂單之後，將庫存量減去訂單的訂購產品數量。
2. 修改產品單價的時後，不得超過原始價值的10%。
3. 如果訂單被刪除了，系統可以寄Mail通知管理人員。
4. 新增訂單時，訂單流水號能夠依照日期所產生，例如:200803270123。
我們要如何建立觸發程序，並達到我們的需求 ?

觸發程序簡易說明：
Trigger Definition：
1. 觸發程序是依附資料表或檢視存在，本身是整個交易的延伸。
2. 從應用程式進行資料處理異動作業開始到觸發程序執行完畢，都屬於同一個交易的範圍。
DML Trigger：
(1) After Trigger：在執行Insert、Update、Delete陳述式的動作之後執行。
(2) Instead of Trigger：取代原先的Insert、Update、Delete的動作。

我們先建立兩個測試用的資料表，並依照需求順序建立對應的觸發程序。
 -- Create table and input data CREATE TABLE Product( [ProductNo] int NOT NULL, [ProductName] nvarchar(10)NOT NULL, [Price] money, [Stock] int, CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED (ProductNo) ); CREATE TABLE OrderDetail( [OrderNo] nvarchar(12) NOT NULL, [ProductNo] int NOT NULL, [Quantity] int NOT NULL, [OrderDate] datetime, CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED (OrderNo), CONSTRAINT [FK_Product_OrderDetail] FOREIGN KEY (ProductNo) REFERENCES Product (ProductNo) ); INSERT INTO Product VALUES (1,'A', 100, 30); INSERT INTO Product VALUES (2,'B', 150, 20); INSERT INTO Product VALUES (3,'C', 200, 10); INSERT INTO OrderDetail VALUES (200801170001, 1, 3, '2008-01-17 12:54:46.587'); INSERT INTO OrderDetail VALUES (200801170002, 2, 5, '2008-01-17 14:32:19.153'); INSERT INTO OrderDetail VALUES (200802020001, 3, 7, '2008-02-02 13:24:35.273'); GO -- AFTER INSERT CREATE TRIGGER Tri_AfterInsert ON OrderDetail AFTER INSERT AS DECLARE @Pid int DECLARE @Qty int SELECT @Pid=ProductNo, @Qty=Quantity FROM INSERTED; UPDATE Product SET Stock=Stock-@Qty WHERE ProductNo = @Pid; GO -- AFTER UPDATE CREATE TRIGGER Tri_AfterUpdate ON Product AFTER UPDATE AS DECLARE @PriceBefore money DECLARE @PriceAfter money DECLARE @Difference money IF UPDATE(Price) BEGIN SET @PriceBefore = (SELECT Price FROM DELETED) SET @PriceAfter = (SELECT Price FROM INSERTED) SET @Difference = (@PriceBefore-@PriceAfter)/@PriceBefore IF (@Difference > 0.1) OR (@Difference < -0.1) BEGIN ROLLBACK END END GO -- AFTER DELETE CREATE TRIGGER Tri_AfterDelete ON OrderDetail AFTER DELETE AS IF (SELECT COUNT(*) FROM DELETED) > 0 BEGIN DECLARE @MSGsubject nvarchar(max) DECLARE @MSGbody nvarchar(max) SET @MSGsubject = (SELECT N'Order form deletion notice. Deleted by ' + SUSER_SNAME()) SET @MSGbody = (SELECT * FROM DELETED FOR XML AUTO, ELEMENTS, ROOT('Data')) EXEC MSDB.dbo.sp_send_dbmail @profile_name='DBA Mail Notice', @recipients = 'eric@mail.dsic.com.tw', @subject = @MSGsubject, @body = @MSGbody, @body_format=TEXT END GO -- INSTEAD OF INSERT CREATE TRIGGER Tri_InsteadOfInsert ON OrderDetail INSTEAD OF INSERT AS DECLARE @oid bigint DECLARE @today smalldatetime DECLARE @count int SELECT @today = CONVERT(datetime,CONVERT(char(10),GETDATE(),111)) SELECT @count = COUNT(*)+1 FROM OrderDetail WHERE OrderDate >= @today AND OrderDate < DateAdd(DAY, 1, @today) SELECT @oid = CONVERT(bigint, CONVERT(nvarchar(10), @today,112))*10000 + @count INSERT INTO OrderDetail SELECT @oid, ProductNo, Quantity, OrderDate FROM INSERTED GO -- Test trigger SELECT * FROM Product SELECT * FROM OrderDetail INSERT INTO OrderDetail (ProductNo, Quantity, OrderDate) VALUES (1, 5,GETDATE()) UPDATE Product SET Price = Price*30 WHERE ProductNo = 1 Delete OrderDetail --Cleaning up environment DROP TABLE OrderDetail DROP TABLE Product

較新的文章較舊的文章首頁