How can I force some data maintenance statements whether the transactions success or

How can I force some data maintenance statements whether the transactions
success or failure?
The case is as:
When the insert statement to do, the insert trigger will execute, when the
triggers has errors, then the triggers should write the custom messages to
the another tables and then rollback the insert statements. The client
sides will access the another tables gather the messages. How to write this
case?Refer these error handlings
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Madhivanan|||Hi, ABC
If you want something logged in a table even when an error appears
(because of a constraint violation, for example), you cannot do this.
If you only want something logged according to a certain condition
which is verified by the trigger, you have two solutions:
a) if it's OK to just ignore the incorrect value, use an "INSTEAD OF"
trigger which updates only the correct values and returns (maybe with a
RAISERROR, but with no ROLLBACK)
b) use a normal trigger, but after RAISERROR and ROLLBACK, insert the
message in the error log table, with something like this:
USE tempdb
SET NOCOUNT ON
GO
CREATE TABLE Employees (
EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(50) NOT NULL UNIQUE,
Salary money NOT NULL CHECK (Salary>0)
)
INSERT INTO Employees VALUES ('Adam', 50000)
INSERT INTO Employees VALUES ('Bob', 70000)
GO
CREATE TABLE ErrorLog (
MessageID int IDENTITY PRIMARY KEY,
EventDate datetime NOT NULL DEFAULT GETDATE(),
UserName varchar(30) NOT NULL DEFAULT SYSTEM_USER,
HostName varchar(30) NOT NULL DEFAULT HOST_NAME(),
Message nvarchar(1000) NOT NULL
)
GO
CREATE TRIGGER Employees_U_SmallRaisesOnly ON Employees
FOR UPDATE
AS
IF @.@.ROWCOUNT>0 AND UPDATE(Salary) BEGIN
SET NOCOUNT ON
IF EXISTS (
SELECT * FROM inserted i
INNER JOIN deleted d ON i.EmployeeID=d.EmployeeID
WHERE i.Salary>d.Salary*2
) BEGIN
DECLARE @.Messages TABLE (Message nvarchar(1000))
INSERT INTO @.Messages (Message)
SELECT 'Someone tried to change the salary for '+i.EmployeeName
+' from '+CONVERT(varchar(23),d.Salary,1)
+' to '+CONVERT(varchar(23),i.Salary,1)+' !'
FROM inserted i
INNER JOIN deleted d ON i.EmployeeID=d.EmployeeID
WHERE i.Salary>d.Salary*2
RAISERROR ('You cannot raise a salary to more than double !',16,1)
ROLLBACK
INSERT INTO ErrorLog (Message)
SELECT Message FROM @.Messages
END
END
GO
UPDATE Employees SET Salary=60000 WHERE EmployeeName='Adam'
GO
SELECT * FROM Employees
SELECT * FROM ErrorLog
GO
UPDATE Employees SET Salary=200000 WHERE EmployeeName='Bob'
GO
SELECT * FROM Employees
SELECT * FROM ErrorLog
GO
DROP TABLE Employees, ErrorLog
Razvan
PS. I don't know if this behaviour is documented and/or supported.|||On Thu, 24 Nov 2005 14:19:17 +0800, ABC wrote:

>How can I force some data maintenance statements whether the transactions
>success or failure?
>The case is as:
>When the insert statement to do, the insert trigger will execute, when the
>triggers has errors, then the triggers should write the custom messages to
>the another tables and then rollback the insert statements. The client
>sides will access the another tables gather the messages. How to write thi
s
>case?
>
Hi ABC,
I know of only two ways to persist something over a ROLLBACK.
If you want to log a single error, best is to use variables:
SET @.ErrorMsg = 'Whatever the error code should be'
SET @.ErrorSrc = 'Something to identify where the error was made'
SET @.ErrorWhen = CURRENT_TIMESTAMP
ROLLBACK TRANSACTION
INSERT INTO Logging (ErrorMsg, ErrorSrc, ErrorWhen)
VALUES (@.ErrorMsg, @.ErrorSrc, @.ErrorWhen)
In this example, the variables are not actually needed since I use
hard-coded strings. But if the error message or error source has to be
taken from inserted or updated rows that will be rolled back, then use
this method.
If you have to log an unknown number of error messages (e.g. a multi-row
update fails and you want to log all rows that vioalte the business
constraint), then use a table variable:
DECLARE @.Errors TABLE(...)
INSERT INTO @.Errors (..., ..., ...)
SELECT ..., ..., ...
FROM inserted
WHERE business rule is violated
ROLLBACK TRANSACTION
INSERT INTO Logging (..., ..., ...)
SELECT ..., ..., ...
FROM @.Errors
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)