Tutorial on SQL Triggers & Stored Procedures

Save

Stored Procedures and Triggers within a database are similar constructs. They can both perform the same SQL statements. The biggest difference between the two is how they are executed. A stored procedure has to be executed by a user, while a trigger is executed by the system as the result of an event. Events that cause triggers to be activated include data inserts, updates and deletes. One drawback to using triggers instead of stored procedures is that they cannot accept parameters.

Stored Procedures

  • Use the statement below to create a basic stored procedure with no parameters.

    CREATE PROCEDURE mySproc
    AS
    BEGIN

    -- SQL Statements

    END

  • Create a stored procedure with parameters by copying the code below. To pass a parameter with a default value, include an equals sign and the value desired.

    CREATE PROCEDURE mySproc
    {

     @Param1 varchar(50),
    @Param2 datetime = NULL,
    @Param3 int = 0

    }
    AS
    BEGIN

    -- SQL Statements

    END

  • Update or modify a stored procedure by using the \"Alter\" command from the example below.

    ALTER PROCEDURE mySproc
    AS
    BEGIN

    -- SQL Statements

    END

  • Remove a stored procedure from the database with the \"Drop\" command as shown below.

    DROP PROCEDURE mySproc;

  • Execute your stored procedure within SQL by running the commands below. If you include parameters, put them in the order that is expected by the stored procedure. Any strings passed in should be in single quotes.

    Example with no parameters:
    EXEC dbo.mySproc
    GO

    Example with parameters:
    EXEC dbo.mySproc 'string data', '1/1/1900', 1;

Triggers

  • Add a basic trigger by copying the code below.

    CREATE TRIGGER myTrigger
    ON myTable
    AFTER INSERT
    AS
    BEGIN

    -- SQL Statements

    END

  • Make a trigger that will run after multiple events with the commands below.

    CREATE TRIGGER myTrigger
    ON myTable
    AFTER INSERT, UPDATE, DELETE
    AS
    BEGIN

    -- SQL Statements

    END

  • Modify your trigger using the \"Alter\" command.

    ALTER TRIGGER myTrigger
    ON myTable
    AFTER INSERT
    AS
    BEGIN

    -- SQL Statements

    END

  • Execute the statements from a trigger instead of the commands that cause the event by using the \"Instead Of\" command. For example, if mySproc updated the table myTable, it would cause an UPDATE event. If you had a trigger set up to catch that event that included an \"Instead Of\" command, the code from the trigger would run, and the code from the stored procedure would never execute.

    CREATE TRIGGER myTrigger
    ON myTable
    INSTEAD OF UPDATE
    AS
    BEGIN

    -- SQL Statements

    END

  • Remove your trigger from the database with the \"Drop\" command as shown below.

    DROP TRIGGER myTrigger;

Tips & Warnings

  • If you need to create triggers that are conditional, you can run one on the update event and use the update command to determine if a specific column has changed.
  • IF NOT UPDATE(myColumn)
  • RETURN

References

  • Photo Credit Opened hard disk drive colose-up toned in blue color image by Roman Sigaev from Fotolia.com
Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!