This section describes how to replace PL/SQL procedures and functions with Transact-SQL stored procedures.
Transact-SQL procedures offer the same capabilities as PL/SQL procedures and functions. PL/SQL style packages are not supported. Transact-SQL procedures can accept and return user-supplied parameters. Integer values can also be returned using the RETURN statement. They are created using the CREATE PROCEDURE statement:
CREATE PROCedure [owner.]procedure_name[;number]'
[(parameter1 [, parameter2]...[parameter255])]
[{FOR REPLICATION} | {WITH RECOMPILE}
Except for temporary procedures, you can only create a stored procedure in the current database. Temporary procedures are created in the tempdb database. A temporary procedure is created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures.
A local temporary procedure can only be used by the user who created it. Permission to execute a local temporary procedure cannot be granted to other users. Local temporary procedures are automatically dropped at the end of the user session.
A global temporary procedure is available to all SQL Server users. If a global temporary procedure is created, all users can access it, and permissions cannot be explicitly revoked. Global temporary procedures are dropped at the end of the last user session using the procedure.
The nesting of Transact-SQL stored procedures is allowed. The nesting level is incremented when the called procedure starts execution, and it is decremented when the called procedure finishes execution. Exceeding the maximum of 16 levels of nesting causes the whole calling procedure chain to fail.
The following example demonstrates how a Transact-SQL stored procedure can be used to replace a packaged function. The Transact-SQL version is much simpler because of SQL Server's ability to return result sets directly from SELECT statements in a stored procedure, without using a cursor.
| Oracle | SQL Server |
| CREATE OR REPLACE PACKAGE STUDENT_ADMIN.P1 AS ROWCOUNT NUMBER :=0; CURSOR C1 RETURN STUDENT%ROWTYPE; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER; END P1; / CREATE OR REPLACE PACKAGE BODY STUDENT_ADMIN.P1 AS CURSOR C1 RETURN STUDENT%ROWTYPE IS SELECT * FROM STUDENT_ADMIN.STUDENT WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE WHERE GRADE.SSN=STUDENT.SSN) ORDER BY SSN; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER IS WORKREC STUDENT%ROWTYPE; BEGIN IF NOT C1%ISOPEN THEN OPEN C1; ROWCOUNT :=0; ENDIF; FETCH C1 INTO WORKREC; IF (C1%NOTFOUND) THEN CLOSE C1; ROWCOUNT :=0; ELSE WORKVAR := WORKREC.FNAME||' '||WORKREC.LNAME|| ', social security number '||WORKREC.SSN||' is not enrolled in any classes!'; ROWCOUNT := ROWCOUNT + 1; ENDIF; RETURN(ROWCOUNT); EXCEPTION WHEN OTHERS THEN IF C1%ISOPEN THEN CLOSE C1; ROWCOUNT :=0; ENDIF; RAISE_APPLICATION_ERROR(-20001,SQLERRM); END SHOW_RELUCTANT_STUDENTS; END P1; / |
CREATE PROCEDURE STUDENT_ADMIN.SHOW_RELUCTANT_STUDENTS AS SELECT FNAME+'' +LNAME+', social security number'+ SSN+' is not enrolled in any classes!' FROM STUDENT_ADMIN.STUDENT S WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE G.SSN=S.SSN) ORDER BY SSN RETURN@@ROWCOUNT GO |
PL/SQL offers row and statement level triggers that can be executed before or after the execution of an INSERT, UPDATE, or DELETE statement. Any number of triggers can be attached to a table. A Transact-SQL trigger is activated only once per statement, even if it affects multiple rows. A complex query containing a WHILE loop can repeat an UPDATE, DELETE, or INSERT many times, and the trigger is activated each time. Constraints are checked prior to trigger execution. If constraints are violated, the trigger is not run.
Transact-SQL provides the INSERTED and DELETED table(s) to track row-level changes. These two tables remove the requirement for row-level triggers. When an INSERT, UPDATE, or DELETE statement is executed, rows are added to the trigger table and to the INSERTED and DELETED table(s) at the same time.
The INSERTED and DELETED tables are identical to the trigger table. They have the same column names and the same data types. For example, if a trigger is placed on the GRADE table, the INSERTED and DELETED tables have this structure.
| GRADE | INSERTED | DELETED |
| SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2) |
SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2) |
SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2) |
The INSERTED and DELETED tables can be examined by the trigger to determine what types of trigger actions should be carried out. The INSERTED table is used with the INSERT and UPDATE statements. The DELETED table is used with DELETE and UPDATE statements.
The UPDATE statement uses both the INSERTED and DELETED tables because SQL Server always deletes the old row and inserts a new row whenever an UPDATE operation is performed. Consequently, whenever an UPDATE is performed, the rows in the INSERTED table are always duplicates of the rows in the DELETED table.
The following example uses the INSERTED and DELETED tables to replace a PL/SQL row-level trigger. Notice that a full outer join is used to bring back all rows from either table.
| Oracle | SQL Server |
| CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES AFTER INSERT OR UPDATE OR DELETE ON STUDENT_ADMIN.GRADE FOR EACH ROW BEGIN INSERT INTO GRADE_HISTORY( TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE, NEW_SSN, NEW_CCODE, NEW_GRADE) VALUES (USER, SYSDATE, :OLD.SSN, :OLD.CCODE, :OLD.GRADE, :NEW.SSN, :NEW.CCODE, :NEW.GRADE), END; |
CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES ON STUDENT_ADMIN.GRADE FOR INSERT, UPDATE, DELETE AS INSERT INTO GRADE_HISTORY( TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE NEW_SSN, NEW_CCODE, NEW_GRADE) SELECT USER, GETDATE(), OLD.SSN, OLD.CCODE, OLD.GRADE, NEW.SSN, NEW.CCODE, NEW.GRADE FROM INSERTED NEW FULL OUTER JOIN DELETED OLD ON NEW.SSN = OLD.SSN |
A table is allowed one trigger for INSERT, one for UPDATE, and one for DELETE. If a trigger is defined for an operation (insert, update, or delete) that already has a trigger association, the existing trigger is replaced. No warning message is given before the replacement occurs. However, the same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.
You can create a trigger only in the current database. A trigger is allowed to reference objects outside the current database. If you use an owner name to qualify a trigger, qualify the table name the same way.
Triggers can be nested 16 levels deep. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level has been exceeded and the trigger is canceled.
A trigger does not call itself in response to a second update to the same table within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger is activated only once.
SQL Server declarative referential integrity (DRI) does not provide cross-database referential integrity. If cross-database referential integrity is required, use triggers for this purpose.
The following SQL statements are not allowed in a trigger:
For more information about triggers, see the Microsoft SQL Server Transact-SQL Reference.