Stored Procedures and Triggers in data base

what do Stored Procedures and Triggers in data base mean ? how can i create Stored Procedures ? how can i crest Triggers ? if you have simple examples for each of these .please help :) what i know is only about trigger which is activated if an action of(insert o开发者_运维百科r delete or update ) violates the constrains specified but i don't know how to create ,so again if any have example please

---

Think of a Stored Procedure as a method in your code. It runs a specific set of instructions.

Stored Procedures are created to, for example, manage complex sets of data that would normally be a pain to handle along in your code.

You can create a Stored Procedure with the following instructions:

Oracle

CREATE OR REPLACE PROCEDURE P_PROCEDURE_NAME (
    pParameter1 NUMBER
    , pParameter2 VARCHAR2(100 Bytes)
) AS
BEGIN
    -- Procedure code here...
END;

SQL Server

CREATE PROCEDURE cspProcedureName 
    @parameter1 int
    , @parameter2 nvarchar(100)
AS
    -- Procedure code here...

Oracle

As for the Triggers, they are sets of code called upon an action occuring to the related table. For instance, in Oracle, there are no INDENTITY columns such as SQL Server offers. Instead, Sequences are used along with Triggers to simulate the same. Hence, you will need to create an Oracle SEQUENCE, then the TRIGGER to update the ID field of your table.

CREATE SEQUENCE SEQ_CUSTOMERS
    MINVALUE 1
    MAXVALUE 65535
    START WITH 1
    INCREMENT BY 1;

CREATE OR REPLACE TRIGGER TRG_CUSTOMERS_INSERT
    BEFORE INSERT 
        ON TBL_CUSTOMERS 
        FOR EACH ROW
BEGIN
    :NEW.CUST_ID := SEQ_CUSTOMERS.NEXTVAL;
END;

SQL Server

A trigger example in SQL Server would be updating automatically the update datetime of a record. Consider the following:

CREATE TABLE Customers (
    CustId int NOT NULL IDENTITY(1, 1) PRIMARY KEY
    , CustName nvarchar(100) NOT NULL
    , CreatedOn datetime DEFAULT GETDATE()
    , LastUpdate datetime NOT NULL
)
GO

CREATE TRIGGER trgCustomersUpdt
    AFTER UPDATE
        ON Customers
AS
    update Customers
        set LastUpdate = GETDATE()
        where CustId = inserted.Custid
GO

DISCLAIMER

This code has not been tested and may require minor changes for it to work properly against its respective RDBMS.

To sum it up, Triggers are mainly used to as illustrated here, despite there are many other possible use, such as building up an history of table changes that occured throught time, keeping all records of transactions into an history table or the like. The Stored Procedures are mainly used to perform complex database tasks where this would get too complex to do in code.