You administer a Microsoft SQL Server 2008 R2 instance configured to use WindowsAuthentication.
The database contains a table named CustomerTransaction that has the following definition:
CREATE TABLE dbo.CustomerTransaction(
CustomerTransactionId int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
TransactionAmount money NOT NULL )
You define the following table:
CREATE TABLE dbo.CustomerWarningLog(
CustomerWarningLogId int NOT NULL identity PRIMARY KEY,
CustomerId int NOT NULL,
Balance money NOT NULL,
LogTime datetime2(0) NOT NULL,
LogUserName nvarchar(128) NOT NULL)
You need to ensure that the following requirements are met:
•An entry is logged in CustomerWarningLog when a customer’s account balance is less than 100.00.
•The Transaction LogUserName is set to the login name of the user who modifies the CustomerTransaction table.
Which Transact-SQL statement or statements should you use?
A. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction
FOR INSERT, UPDATE, DELETE
AS
IF UPDATE(CustomerID)
WITH TotalCTE AS
(SELECT CustomerId, SUM(TransactionAmount) AS Balance
FROM dbo.CustomerTransaction
WHERE CustomerId in (SELECT CustomerId
FROM inserted
UNION ALL
SELECT CustomerId
FROM deleted)
GROUP BY CustomerId)
INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogTime, LogUserName)
SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()
FROM TotalCTE WHERE TotalCTE.Balance < 100
GO
B. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateTrigger ON dbo.CustomerTransaction
FOR INSERT, UPDATE
AS
IF UPDATE(CustomerID) or UPDATE(TransactionAmount)
WITH TotalCTE AS
(SELECT CustomerId, SUM(TransactionAmount) AS Balance
FROM dbo.CustomerTransaction
WHERE CustomerId in (SELECT CustomerId
FROM inserted
UNION ALL
SELECT CustomerId
FROM deleted)
GROUP BY CustomerId)
INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogTime, LogUserName)
SELECT CustomerId, Balance, SYSDATETIME(), USER_NAME()
FROM TotalCTE WHERE TotalCTE.Balance < 100
GO
C. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction
FOR INSERT, UPDATE, DELETE
AS
DECLARE @CustomerId int, @Balance money
WITH TotalCTE AS
(SELECT CustomerId, SUM(TransactionAmount) AS Balance
FROM dbo.CustomerTransaction
WHERE CustomerId in (SELECT CustomerId
FROM inserted
UNION ALL
SELECT CustomerId
FROM deleted)
GROUP BY CustomerId)
SELECT @CustomerId = CustomerId, @Balance = Balance
FROM TotalCTE WHERE TotalCTE.Balance < 100
IF @@rowcount > 0
INSERT dbo. CustomerWarningLog(CustomerId, Balance, LogT ime,LogUserName)
SELECT @CustomerId, @Balance, SYSDATETIME(), SUSER_SNAME()
GO
D. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction
FOR INSERT, UPDATE, DELETE
AS
IF UPDATE(CustomerID) or UPDATE(TransactionAmount)
WITH TotalCTE AS
(SELECT CustomerId, SUM(TransactionAmount) AS Balance
FROM dbo.CustomerTransaction
WHERE CustomerId in (SELECT CustomerId
FROM inserted
UNION ALL
SELECT CustomerId
FROM deleted)
GROUP BY CustomerId)
INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogT ime, LogUserName)
SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()
FROM TotalCTE WHERE TotalCTE.Balance < 100
GO
E. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction
FOR INSERT, UPDATE, DELETE
AS
DECLARE @CustomerId int, @Balance money
WITH TotalCTE AS
(SELECT CustomerId, SUM(TransactionAmount) AS Balance
FROM dbo.CustomerTransaction
WHERE CustomerId in (SELECT CustomerId
FROM inserted
UNION ALL
SELECT CustomerId
FROM deleted)
GROUP BY CustomerId)
SELECT @CustomerId = CustomerId, @Balance = Balance
FROM TotalCTE WHERE TotalCTE.Balance < 100
IF @CustomerId IS NULL
INSERT dbo. CustomerWarningLog(CustomerId, Balance, LogTime, LogUserName)
SELECT @CustomerId, @Balance, SYSDATETIME(), USER_NAME()
GO
F. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction
FOR INSERT, UPDATE, DELETE
AS
WITH TotalCTE AS
(SELECT CustomerId, SUM(TransactionAmount) AS Balance
FROM dbo.CustomerTransaction
WHERE CustomerId in (SELECT CustomerId
FROM inserted)
GROUP BY CustomerId)
INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogTime, LogUserName)
SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()
FROM TotalCTE WHERE TotalCTE.Balance <= 100
GO
G. CREATE TRIGGER dbo.CustomerTransaction_UpdateDeleteTrigger ON dbo.CustomerTransaction
FOR UPDATE, DELETE
AS
WITH TotalCTE AS
(SELECT CustomerId, SUM(TransactionAmount) AS Balance
FROM dbo.CustomerTransaction
WHERE TransactionAmount < 100
AND CustomerId in (SELECT CustomerId
FROM deleted)
GROUP BY CustomerId)
INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogTime, LogUserName)
SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()
FROM TotalCTE
GO
H. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction
FOR INSERT, UPDATE, DELETE
AS
WITH TotalCTE AS
(SELECT CustomerId, SUM(TransactionAmount) AS Balance
FROM dbo.CustomerTransaction
WHERE CustomerId in (SELECT CustomerId
FROM inserted
UNION ALL
SELECT CustomerId
FROM deleted)
GROUP BY CustomerId)
INSERT dbo. CustomerWarningLog(CustomerId, Balance, LogTime, LogUserName)
SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()
FROM TotalCTE WHERE TotalCTE.Balance < 100
GO
