Owner: | Purchasing |
Table/View: | Purchasing.PurchaseOrderHeader |
Disabled: | |
Encrypted: | |
Creation Date: | 04/26/2006 |
Modification Date: | 04/26/2006 |
Description: | AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table. |
QUOTED_IDENTIFIER: | |
ANSI_NULLS: |
Instead of: | |
Insert: | |
Update: | |
Delete: |
Objects that [Purchasing].[uPurchaseOrderHeader] depends on
Object Name | Owner | Object Type | Dep Level | |
AccountNumber | dbo | User Defined type | 1 | |
Flag | dbo | User Defined type | 1 | |
Name | dbo | User Defined type | 1 | |
NameStyle | dbo | User Defined type | 1 | |
Phone | dbo | User Defined type | 1 | |
Contact | Person | Table | 2 | |
ErrorLog | dbo | Table | 2 | |
ShipMethod | Purchasing | Table | 2 | |
Vendor | Purchasing | Table | 2 | |
uspPrintError | dbo | Procedure | 2 | |
Employee | HumanResources | Table | 3 | |
uspLogError | dbo | Procedure | 3 | |
PurchaseOrderHeader | Purchasing | Table | 4 |
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader]
AFTER UPDATE AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
-- Update RevisionNumber for modification of any field EXCEPT the Status.
IF NOT UPDATE([Status])
BEGIN
UPDATE [Purchasing].[PurchaseOrderHeader]
SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] =
[Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1
WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN
(SELECT inserted.[PurchaseOrderID] FROM inserted);
END;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
GO
SET QUOTED_IDENTIFIER OFF
GO
GO
SET ANSI_NULLS OFF
GO
See Also
List of triggers