Implementing Stored Procedures and Triggers

 

1.  What is the syntax of the CREATE PROCEDURE and DROP PROCEDURE statements?

     CREATE PROCEDURE statement is used to create a stored procedure, and syntax is:

                                                CREATE PROCEDURE <procedure name>  [number]

<parameter list>

[WITH <RECOMPILE or ENCRYPTION or RECOMPILE, ENCRYPTION>]

AS <sql_code>

     DROP PROCEDURE statement deleting the stored procedures that are not required, and the syntax is:

                                                DROP PROCEDURE <procedure_name>

2.  What are the types of stored procedures? 

      There are two types of stored procedures:

• System stored procedures
• Catalog stored procedures

3.  Which system stored procedure enables user to add a user-defined data type?

     The Sp_addtype enables user to add a user-defined data type.

4.  How are the system stored procedures created?

     System stored procedures are predefined and created as part of SQL Server installation.

5.  Can Triggers be used to implement referential integrity on tables?

     Yes, you can use triggers to implement referential integrity checks in a table.

6.  How to execute stored procedures?

     EXECUTE procedure_name  [parameter [, parameter2  […,  parameter 255]]]

     WITH RECOMPILE 

7.  What is the use of the WITH RECOMPILE option in the CREATE PROCEDURE statement?

      The WITH RECOMPILE option indicates that the stored procedure is compiled every time it is executed. This option causes SQL Server to generate an execution plan and recompile the stored procedure for the current run only. The generated execution plan is left in the cache for future executions.

8.  What are the two temporary tables used by triggers?

      The two temporary tables used by triggers are: deleted table and inserted table.

9.What is the function of the UPDATE trigger? 

      UPDATE triggers are invoked automatically when the table data is modified. UPDATE triggers use a deleted table, which stores the pre-image of data to be modified, and an inserted table, which stores the post-image of the data modified in a UPDATE operation. Pre-image data is original row before modification, and post-image data is the new row after modification. After the UPDATE operation, the inserted and deleted tables are used to update the table data.

10.Can Triggers be used as an alternative to the REFERENCES and FOREIGN KEY constraints?

      Yes, you can use triggers to implement referential integrity checks in a table, which can also be done by using the REFERENCES and FOREIGN KEY constraints.

11.What are the four catalog stored procedures and what is their use?

     Catalog procedures are predefined procedures in SQL Server 2000. Catalog stored procedures provide a standard catalog interface to the client application accessing the database to retrieve information from the server catalog. For instance:

• Sp_column_privileges – displays the column privilege information of a particular table
• Sp_columns – displays the information of particular column of a table
• Sp_keys – displays the primary key details of a particular table
• Sp_stored_procedures – displays the stored procedures accessible in SQL Server 2000.

12.Is the data stored in the inserted table, in the DELETE trigger?

     No, the UPDATE triggers use a deleted table, which stores the pre-image of data to be modified, and an inserted table, which stores the post-image of the data modified in an UPDATE operation.

13.What is the syntax of the ALTER PROCEDURE statement?

      ALTER PROCEDURE <procedure_name>

              [ { @ parameter_name1 data_type} ]

              [ { @ parameter_name2 data_type} ]

              [ { @ parameter_name3 data_type} ]

      AS

      <SQL statement>