how to create trigger in MS SQL 2005

Question

I need to update the record status(field name IsActivate-this holds the status of record) but needs to check first if the data is used in another table(meaning table is having a relationship with another table).If the data that I want to update is used in another table, update is not allowed else it should be updated.How can I can create trigger to achieved this approach using MS SQL 2005?Or this can be achieved using SP?How?

Answer 1

I would avoid using a trigger for this if i were you. You can easily do this in an SP.

Your SP for updating the record should do something like this:-

• I assume that the value you want to check is being passed to update SP, likely in a parameter eg @value
• Use @value in a SELECT statment in something like SELECT @result = count(1) FROM tableToCheck WHERE columnToCheck = @value
• If @result = 0, go ahead and fire your existing UPDATE statement
• If @result <> 0, do whatever you need to do in your business scenario like maybe update an Output param to indicate that your update failed

In case you want to know the syntax of the above logic, you can look up MSDN if you are using SQL Server or the MySQL docs if using MySQL or Oracle help if you are using Oracle. If any other engine, just google "CREATE PROCEDURE <engineName>"

And then if you have any specific questions with the syntax, feel free to post them here with your code and we will be glad to help you with them!!