How to prevent updating certain rows in SQL Server 2012?

Question

I'm trying to perform a query that will unable the user to update certain rows with a condition. I know this is done by trigger but I'm still a beginner and I don't know how and how to activate or call that trigger in c# windows forms.

This is my table for example:

tbl_Products (prod_ID, prod_Name, Quantity, Price, Status)

My condition will be for example like:

deny update tbl_products (prod_ID, prod_Name, Quantity, Price) where status = 'sold'

The user will be able to update the product details only when the column status ='no sold'

Answer 1

I don't know how and how to activate or call that trigger in c# windows forms.

You don't; triggers are applied at the database server, and will be invoked automatically by UPDATE statements. If you've created a trigger that effectively breaks UPDATE, then... I guess that'll work.

However, usually you simply (at the application level)... don't update the rows you don't wish to update.

Another option, for reference, is to essentially have two tables - for example PENDING_PRODUCTS and SOLD_PRODUCTS - and then REVOKE UPDATE ON SOLD_PRODUCTS (and probably DELETE too), so that you can INSERT into it only - and perhaps create a VIEW (PRODUCTS) that is a union of the two with a dummy status column that discriminates between them.

Answer 2

First thing you cant call or execute triggers explicitly , the triggers are called automatically . In your scenario you can write a logic inside trigger so that it checks the condition then update the table , https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017 visit this link for more info on triggers

Answer 3

If you do want to go down the trigger route, you will need to write a trigger something like this:

CREATE TRIGGER StopUpdateSold
ON tbl_Prosucts
AFTER UPDATE
AS 
BEGIN
    IF EXISTS (SELECT 1 FROM deleted where status = 'sold')
    BEGIN
        RAISERROR ('You cannot update when Status is sold', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;

Now when attempting to perform an update from your c# code, you will need to catch the error raised by SQL Server. As others have said, you do not call the trigger explicitly. The trigger fires when the update occurs, and rolls the update back, if it hits the condition, at the same time firing an exception, which you then catch in order to provide meaningful information to the user.

Personally, however, I would disable the update function within your c# code for any record with status 'sold'. I.e. I would have the business logic in the application rather than the database. This is not a 'hard and fast' rule: there are occasions for including business logic via triggers, particularly where users access the database in many different ways.