CODEWITHSUNDEEP
sqlsql-servert-sql
Madao
Madao

Reputation: 316

How to fix If condition with rollback transaction in Trigger in SQL Server

I'm doing my homework and i'm trying to create a trigger with if condition. But that Trigger doesn't work properly.

This is for my homework. Im using Northwind Database in my training.

Our teacher has asked us to create a trigger in Products table. Her question is :

"While inserting data into a Product Table; If inserted data has the same product name in Products table, cancel this transaction and print this warn message : 'That product name has already in my table' warning'"

Here is my code:

create trigger AyniUrun
on products
after insert
as
begin
    declare @insertedProductName nvarchar (250) =  (select ProductName from inserted)
    if exists (select ProductName from Products where ProductName = @insertedProductName)
        begin
            print 'AYNI İSİMDE ÜRÜN VAR!'
            rollback
        end 
end

When I try to insert the same named product into Products Table, it gives me that message : "The transaction ended in the trigger. The batch has been aborted.". This is what i want in this condition.

But when I try to insert a different named product into Products Table, it also gives me same message and terminates my transaction. Where did i go wrong ?

Upvotes: 0

Views: 3604

Answers (3)

GMB
GMB

Reputation: 222462

It is possible to simplify the code of your trigger. However : as it is, this will not do what you expect :

begin
    print 'AYNI İSİMDE ÜRÜN VAR!'
    rollback
    ...

Instead, you need to :

begin
    RAISERROR('AYNI İSİMDE ÜRÜN VAR!')
    rollback
    ...

Upvotes: 0

Madao
Madao

Reputation: 316

Thank you very much for answers. Today we solved this question in class: 

create trigger AyniUrun
on products
after insert
as
begin
    declare @insertedProductName nvarchar (250) =  (select ProductName from inserted)
    declare @counter int = (select COUNT(*) from Products where ProductName = @insertedProductName)
    print @counter
    if @counter > 1
        rollback
end

Upvotes: 0

Gaurav
Gaurav

Reputation: 623

You need to use join between inserted table and product table to determine the existence. Please try below- 

   CREATE TRIGGER MyTrigger ON products
   AFTER INSERT
   AS

    if exists (select * from products p inner join inserted i on p.name=i.name)
    begin
      rollback
      RAISERROR ('Duplicate Data', 16, 1);
   end
   go

Upvotes: 2

Related Questions