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:

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

Dynamic WHERE & Dates
with(updlock) - How many times on same table in single update?
Composing a date from date parts
Delete Query ?
query metadata within trigger scope