What are triggers and stored procedures in a database ?

Question

Can someone explain this to me in beginner mode ?

Ten points for sure if you help me out !

Thanks.

Answer 1

A stored procedure is a program that is stored and ran inside the DBMS (i.e., MySQL, Oracle, SQL Server). In my experience (MySQL, Oracle), stored procedures look like a Pascal program mixed with some SQL statements. Typically, they are used to perform specialized tasks that need to be executed as quickly as possible, perhaps because the program forms a performance bottleneck. Stored procedures can be called from outside the database.



A trigger is also a program that is stored and ran inside the DBMS, but it is executed automatically when a condition is met. For example, if you want each record inside a table to adhere to certain standards that cannot be enforced by the way the table is defined, then you can write a program that is executed, by the DBMS, automatically when a record is inserted into that table. The program can then check if the standards are adhered to (or not) by the record that is about to be inserted, and then insert (or reject) the record into the table.

Answer 2

A 'Stored Procedure' is a way to put SQL inside a SQL database. If you are coming from the PHP/MySQL world or the ASP.NET/MSSQL world, this is probably a foreign concept. PHP in particular tends to encourage programmers to write SQL by splicing together strings. You can do this, and it works. But it's not a very good practice.



Putting SQL inside the database has a number of advantages. Stored procedures act very much like functions in traditional programming languages. A stored proc takes some parameters, and returns a result based on those parameters. It's harder to set up, so why would you want to make the effort? Stored procs have 2 advantages over string splicing: They are more secure. They are *NOT* vulnerable to SQL injection attacks (in general. There are exceptions, but they are rare). They are faster. Most SQL optimizers do a much better job with stored proc code than they do with AD HOC (string splicing) queries.



Triggers are similar to stored procs, except they are defined at the table level. Suppose you want to "do something" any time someone inserts or updates a row in a table. Triggers let you do that. You can (sort of) think of it as a stored proc that is called EVERY TIME a row is modified. It doesn't matter if the row modification came from some PhP string splicing, or from another stored proc. The trigger code will run and do 'whatever it is you wanted to do'.



Hum..that probably wasn't a beginner mode answer. Best I can do. It isn't easy stuff...but I would encourage you to learn it. Real programmers don't do SQL by splicing together strings they get from a web browser. They use stored procedures and (to a lesser extent) triggers.



HTH

Answer 3

What kind of database, Mysql, IBM, Sql is the standard, anyway...



A database triggers is stored PL/SQL program unit associated with a specific database table or view. The code in the trigger defines the action the database needs to perform whenever some database manipulation (INSERT, UPDATE, DELETE) takes place.



So in layman terms it is simply pre-programmed reaction when the user is performing a task on the database, to optimize the user's time.





Stored Procedures are like variables, that store commands for example...



exec usp_displayallusers

usp_ defines that this is user created while...



displayallusers would than run the code



SELECT * FROM USERLIST



Thus displaying all the users.



It is simply custom user created commands created by the user to make running the database simplier, and can help when you need a list of things done multiple times that you can save in one of these stored procedures.