What is a Trigger?
A Trigger is a stored procedure that is associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.
Trigger can not be invoked on demand . Trigger can be used for referential integrity checks also , but wherever possible, constraints should be used for this purpose , instead of triggers , as constraints are much faster.
What is a Stored Procedure?
A stored procedure is a set of pre-compiled T-SQL statemenets, which can be executed whenever required .They are similar to procedure in any programming language and hence can accept input parameters , return output parameters and even status value .
Advantages of using Stored Procedure :
1) Increased modularized programming .
2) Faster execution as they are pre-compiled.
What is the difference between Trigger and Stored Procedure?
Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with queries whereas stored procedure can be called as per user requirement .
Example of automatic execution of Trigger on the compilation of DML statement.
We have a database in which we have store the information about product. The price of a product changes constantly . It is important to maintain the history of the prices of the product . So we will need trigger to update the history of prices of product . Lets see how it works :
Suppose we have the following tables in our database.
(Product)
(Product_Price_History)
Now we will create the price_history_trigger and execute it .
Lets update the product table :
Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
Example of on demand execution of Stored Procedure
Below we are creating the procedure named employer_detail which will give the informtiion about employee.
Now we will call the procedure . There are two ways :
1) From the SQL prompt.
EXECUTE [or EXEC] procedure_name;
2) Within another procedure – simply use the procedure name.
procedure_name;
Please comment if you like the above post or if you find any mistake.
A Trigger is a stored procedure that is associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.
Trigger can not be invoked on demand . Trigger can be used for referential integrity checks also , but wherever possible, constraints should be used for this purpose , instead of triggers , as constraints are much faster.
What is a Stored Procedure?
A stored procedure is a set of pre-compiled T-SQL statemenets, which can be executed whenever required .They are similar to procedure in any programming language and hence can accept input parameters , return output parameters and even status value .
Advantages of using Stored Procedure :
1) Increased modularized programming .
2) Faster execution as they are pre-compiled.
What is the difference between Trigger and Stored Procedure?
Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with queries whereas stored procedure can be called as per user requirement .
Example of automatic execution of Trigger on the compilation of DML statement.
We have a database in which we have store the information about product. The price of a product changes constantly . It is important to maintain the history of the prices of the product . So we will need trigger to update the history of prices of product . Lets see how it works :
Suppose we have the following tables in our database.
| product_name | product_id | supplier_name | product_price |
| product_name | product_id | supplier_name | product_price |
Now we will create the price_history_trigger and execute it .
CREATE or REPLACE TRIGGER
price_history_trigger
BEFORE UPDATE Of product_price
ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
( : old.product_name,
:old_product_id.
:old.supplier_name,
:old.product_price);
END;
Lets update the product table :
UPDATE PRODUCT SET unit_price = 800 WHERE
product_id = 100
Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
Example of on demand execution of Stored Procedure
Below we are creating the procedure named employer_detail which will give the informtiion about employee.
CREATE OR REPLACE PROCEDURE
employer_details
IS
CURSOR emp_cur IS
SELECT first_name, last_name, salary
FROM emp_tbl;
emp_rec emp_cur%rowtype;
BEGIN
FOR emp_rec in sales_cur
LOOP
dbms_output.put_line(emp_cur.first_name
|| ' ' ||emp_cur.last_name
|| ' ' ||emp_cur.salary);
END LOOP;
END;
Now we will call the procedure . There are two ways :
1) From the SQL prompt.
EXECUTE [or EXEC] procedure_name;
2) Within another procedure – simply use the procedure name.
procedure_name;
Please comment if you like the above post or if you find any mistake.
No comments:
Post a Comment