CODEWITHSUNDEEP
sqlsql-server
Mythul
Mythul

Reputation: 1807

How to stop a Transaction in SQL Server 2008?

How to stop a transaction if an if condition fails?

I want this transaction to stop if IF (@dela = '01-01-2013') is true, but instead it raises the error and procedes with the Update execution.

CREATE TRIGGER TR_AdaugareModificareOfertaSpeciala
ON OferteSpeciale
FOR UPDATE, INSERT
AS
BEGIN TRAN T1;
DECLARE @dela DATETIME;
SET @dela = (SELECT dela FROM INSERTED);

IF (UPDATE(codP) OR UPDATE(codM) OR UPDATE(dela))
BEGIN
RAISERROR('Nu se poate modifica cheia primara.', 1, 1);
ROLLBACK TRAN T1;
END

SAVE TRANSACTION T1;
IF (@dela = '01-01-2013')
BEGIN
RAISERROR('Data nu este corecta.', 1, 1);
ROLLBACK TRAN T1;
END

Example where dela = '01-01-2013':

UPDATE OferteSpeciale SET pret = 23.69 where codP = 'P1' and codM = 'M1';

It raises the error but also makes the update.

Thank you.

Upvotes: 1

Views: 10571

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

You have to know that SQL Server DML triggers are always set based and not row based. So, inserted and deleted tables may contain more rows, not just one row.

If you want to cancel the update/insert statement when (@dela = '01-01-2013') then you could use this condition:

IF EXISTS(SELECT * FROM inserted WHERE dela = '20130101')
BEGIN
ROLLBACK;
RAISERROR('The starting date is wrong', 16, 1);
END

Note 1: [SMALL]DATE[TIME][2] constants should follow ISO8601 rules: yyyymmdd, yyyymmdd hh:mm:ss or yyyy-mm-dd, yyyy-mm-ddThh:mm:ss.

Note 2: The error message from 

IF (UPDATE(codP) OR UPDATE(codM) OR UPDATE(dela))
BEGIN
RAISERROR('Nu se poate modifica cheia primara.', 1, 1);
ROLLBACK TRAN T1;
END

is misleading if the PK doesn't contains codP, codM and dela columns.

Note 3: The severity level for RAISERROR statement should be 16 not 1. A RAISERROR statement with a severity level equal to 1 is pretty much like the PRINT statement.

Upvotes: 2

larsts
larsts

Reputation: 451

I believe you could do this with an INSTEAD OF UPDATE trigger.

CREATE TRIGGER TR_AdaugareModificareOfertaSpeciala
ON OferteSpeciale
INSTEAD OF UPDATE
AS

DECLARE @dela DATETIME;
SET @dela = (SELECT dela FROM INSERTED);

IF (UPDATE(codP) OR UPDATE(codM) OR UPDATE(dela)) BEGIN
    RAISERROR('Nu se poate modifica cheia primara.', 1, 1);
END

ELSE IF (@dela = '01-01-2013') BEGIN
    RAISERROR('Data nu este corecta.', 1, 1);
END
ELSE BEGIN 
    UPDATE o SET dela = i.dela, codm = i.codm, codp = i.codp, pret = i.pret-- add rest of columns here
    FROM OferteSpeciale o
        JOIN Inserted i ON o.[Primarykey] = i.[Primarykey]
END

Upvotes: 2

Semih Yagcioglu
Semih Yagcioglu

Reputation: 4101

By default transactions are auto committed but you can SET IMPLICIT_TRANSACTIONS ON; and then at the end of your query you can COMMIT T1; Have a look at this.

Upvotes: 1

Related Questions