【SQL开源代码栏目提醒】:网学会员SQL开源代码为您提供LocalDataCache1.dbo.Customers.sql参考,解决您在LocalDataCache1.dbo.Customers.sql学习中工作中的难题,参考学习。
/****
姝?SQL 鑴氭湰鐢扁滈厤缃暟鎹悓姝モ濆璇濇鐢熸垚銆?
姝よ剼鏈寘鍚湪鏈嶅姟鍣ㄦ暟鎹簱涓婂垱寤烘洿鏀硅窡韪垪銆佸凡鍒犻櫎
椤硅〃鍜岃Е鍙戝櫒鐨勮鍙ャ傝繖浜涙暟鎹簱瀵硅薄瀵逛簬鍚屾鏈嶅姟鍦?
瀹㈡埛绔拰鏈嶅姟鍣ㄦ暟鎹簱涔嬮棿杩涜鎴愬姛鍚屾鏄繀闇鐨勩?
鏈夊叧璇︾粏淇℃伅锛岃鍙傞槄甯姪涓殑鈥滃浣? 閰嶇疆鏁版嵁搴?
鏈嶅姟鍣ㄨ繘琛屽悓姝モ濅富棰樸?
****/
IF @@TRANCOUNT > 0
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
BEGIN TRANSACTION;
IF @@TRANCOUNT > 0
ALTER TABLE [dbo].[Customers]
ADD [LastEditDate] DateTime NULL CONSTRAINT [DF_Customers_LastEditDate] DEFAULT (GETUTCDATE()) WITH VALUES
GO
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
IF @@TRANCOUNT > 0
ALTER TABLE [dbo].[Customers]
ADD [CreationDate] DateTime NULL CONSTRAINT [DF_Customers_CreationDate] DEFAULT (GETUTCDATE()) WITH VALUES
GO
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
IF @@TRANCOUNT > 0
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Customers_Tombstone]'))
BEGIN
CREATE TABLE [dbo].[Customers_Tombstone](
[CustomerID] NChar(5) NOT NULL,
[DeletionDate] DateTime NULL
) ON [PRIMARY]
END
GO
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
IF @@TRANCOUNT > 0
ALTER TABLE [dbo].[Customers_Tombstone] ADD CONSTRAINT [PKDEL_Customers_Tombstone_CustomerID]
PRIMARY KEY CLUSTERED
([CustomerID])
ON [PRIMARY]
GO
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
IF @@TRANCOUNT > 0
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Customers_DeletionTrigger]') AND type = 'TR')
DROP TRIGGER [dbo].[Customers_DeletionTrigger]
GO
CREATE TRIGGER [dbo].[Customers_DeletionTrigger]
ON [dbo].[Customers]
AFTER DELETE
AS
SET NOCOUNT ON
UPDATE [dbo].[Customers_Tombstone]
SET [DeletionDate] = GETUTCDATE()
FROM deleted
WHERE deleted.[CustomerID] = [dbo].[Customers_Tombstone].[CustomerID]
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [dbo].[Customers_Tombstone]
([CustomerID], DeletionDate)
SELECT [CustomerID], GETUTCDATE()
FROM deleted
END
GO
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
IF @@TRANCOUNT > 0
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Customers_UpdateTrigger]') AND type = 'TR')
DROP TRIGGER [dbo].[Customers_UpdateTrigger]
GO
CREATE TRIGGER [dbo].[Customers_UpdateTrigger]
ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[Customers]
SET [LastEditDate] = GETUTCDATE()
FROM inserted
WHERE inserted.[CustomerID] = [dbo].[Customers].[CustomerID]
END;
GO
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
IF @@TRANCOUNT > 0
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Customers_InsertTrigger]') AND type = 'TR')
DROP TRIGGER [dbo].[Customers_InsertTrigger]
GO
CREATE TRIGGER [dbo].[Customers_InsertTrigger]
ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[Customers]
SET [CreationDate] = GETUTCDATE()
FROM inserted
WHERE inserted.[CustomerID] = [dbo].[Customers].[CustomerID]
END;
GO
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
COMMIT TRANSACTION;