Click here to Skip to main content
14,920,009 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
i have a stored procedure that updates a flag in a table and a trigger that deletes the row updated and insert it in a new table.. so the same functionality of the trigger can be added in the stored procedure.. so i just wanna know:

what is better to use stored procedure or trigger? and in what cases? in other words can you give me the advantages and disadvantages of each one

note that I'm using SQL server 2008 and I'm connecting vb.net to my database

thank you in advance
Posted

A trigger is a special type of stored procedure that is not called directly by a user. It will be invoked on some database operations (insert,update,delete)

Refer this link

Differences between a Stored Procedure and a Trigger[^]

It states that:
Quote:
- We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined.

- We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which the action (insert, delete, and update) defined within a trigger can initiate execution of another trigger defined on the same table or a different table.

- Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.

- Stored procedure can take input parameters, but we can't pass parameters as input to a trigger.

- Stored procedures can return values but a trigger cannot return a value.

- We can use Print commands inside a stored procedure for debugging purposes but we can't use print commands inside a trigger.

- We can use transaction statements like begin transaction, commit transaction, and rollback inside a stored procedure but we can't use transaction statements inside a trigger.

- We can call a stored procedure from the front end (.asp files, .aspx files, .ascx files, etc.) but we can't call a trigger from these files.

- Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks. They can have parameters and return multiple results sets.

- The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.


Further,refer to these links

what are the advantages of using stored procedures in sql server[^]

What are advantages and disadvantages of using trigger in SQL Server?[^]

Regards..:)
   
Hi,

I add few more thing to above

When ever you want automatic action should be perform for data integrity at that time you need to use Trigger. Trigger gets fired on DML Operations.

or

You can also use System level Trigger to Monitor any change in DB schema of any database and send email alert to admin if any change takes place.

or

Have you Heard about Magic(Logical) tables ,Trigger utilizes these tables in many ways .

Inserted and Deleted logical tables in SQL Server, These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.

Use of logical tables

Basically, logical tables are used by triggers for the following purpose:
1.
To test data manipulation errors and take suitable actions based on the errors.

2.
To find the difference between the state of a table before and after the data modification and take actions based on that difference


Where stored procedures can be used within your code of .Net,can not execute on DML operation, or you can use it in SQL Agent for regular execution on schedule, or you need to execute it manually..using SSMS.

Regards,
Mubin
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900