How to write insert/update/delete in one trigger in sqlserver 2008 R2

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Actually i had a functionality to write a trigger on one Table A. when ever i Insert record into that table i have some logic to apply and same like update and delete also . i had three different logic's to apply in three different cases (insert,update,delete). i am achieving this functionality with writing three different triggers . but my doubt is can i achieve three cases in one trigger.

Posted 30-Jun-15 2:10am

ntitish

Add a Solution

Comments

virusstorm 30-Jun-15 11:37am

Without knowing what your logic is, I can't say if it is doable or not. Depending on what you are trying to achieve, a MERGE statement might give you the functionality you are seeking.

Personally, I would avoid the trigger route at all cost and write stored procedures. Anytime a trigger performs actions on another table, you can get your self into bad race and blocking conditions. We won't even talk about the headaches of trigger recursion. I urge you to strong reconsider using triggers.

ntitish 1-Jul-15 0:35am

Actually my functionality is like when i insert records in Table-A in DB-A i have to insert same records in Table-B in DB-B. same operation in (update,delete) cases. the DB-A and DB-B are under in same server. with out using trigger how can i achieve this functionality.

virusstorm 1-Jul-15 7:35am

There are lots of ways. Stored procedures are still an option, but ideally, you should take a look at SQL Server Replication. SQL Server has built in tools and technologies to keep multiple databases in sync with each other. If the databases are not an exact copy (in both data and structure), you could also look at using SQL Server Integration Services (SSIS).

ntitish 2-Jul-15 3:00am

if i use stored procedures how can i fire the proc after every insert/update /delete in a table if possible can you please give any example ... bec till now i not used such type of functionality..

virusstorm 2-Jul-15 13:48pm

All of your insert, update, and delete commands would be in a stored procedure. So your clients and applications would all use the procedures and inside those procedures, you would have the SQL that would update the tables in the affected databases.

Save the stored procedure option as a last resort. Take some time and seriously investigate SQL Server Replication technologies and SQL Server Integration Services. These tools are way more reliable then a stored procedure.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)