User-defined Integrity

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories.

Stored Procedures

Microsoft SQL Server stored procedures use the CREATE PROCEDURE statement to accept and return user-supplied parameters. With the exception of temporary stored procedures, stored procedures are created in the current database. The table shows the syntax for Oracle and SQL Server.

Oracle	Microsoft SQL Server
CREATE OR REPLACE PROCEDURE [user.]procedure [(argument [IN \| OUT] datatype [, argument [IN \| OUT] datatype] {IS \| AS} block	CREATE PROC[EDURE] procedure_name [;number] [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [,…n] [WITH { RECOMPILE \| ENCRYPTION \| RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS sql_statement […n]

In SQL Server, temporary procedures are created in the tempdb database by prefacing 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 be used only 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.

SQL Server stored procedures can be nested up to 32 levels. The nesting level is incremented when the called procedure starts execution, and it is decremented when the called procedure finishes execution.

The following example demonstrates how a Transact-SQL stored procedure can be used to replace an Oracle PL/SQL 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	Microsoft 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

Oracle

Microsoft 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

SQL Server does not support constructs similar to Oracle packages or functions, and does not support the CREATE OR REPLACE option for creating stored procedures.

Delaying the Execution of a Stored Procedure

Microsoft SQL Server provides WAITFOR, which allows developers to specify a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction. This is the Transact-SQL equivalent to the Oracle dbms_lock.sleep.

WAITFOR {DELAY 'time' | TIME 'time'}

where

DELAY: Instructs Microsoft SQL Server to wait until the specified amount of time has passed, up to a maximum of 24 hours.
'time': The amount of time to wait. time can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the data portion of the datetime value is not allowed.
TIME: Instructs SQL Server to wait until the specified time.

For example:

BEGIN

    WAITFOR TIME '22:20'

    EXECUTE update_all_stats

END

Specifying Parameters in a Stored Procedure

To specify a parameter within a stored procedure, use this syntax.

Oracle	Microsoft SQL Server
Varname datatype DEFAULT <value>;	{@parameter data_type} [VARYING] [= default] [OUTPUT]

Triggers

Both Oracle and Microsoft SQL Server have triggers, which have some differences in their implementations.

Description	Oracle	Microsoft SQL Server
Number of triggers per table	Unlimited	Unlimited
Triggers executed before INSERT, UPDATE, DELETE	Yes	No
Triggers executed after INSERT, UPDATE, DELETE	Yes	Yes
Statement-level triggers	Yes	Yes
Row-level triggers	Yes	No
Constraints checked prior to execution	Yes, unless trigger is disabled.	Yes. In addition, this is an option in Data Transformation Services.
Referring to old or previous values in an UPDATE or DELETE trigger	:old	DELETED.column
Referring to new values in an INSERT trigger	:new	INSERTED.column
Disabling triggers	ALTER TRIGGER	Option in Data Transformation Services

DELETED and INSERTED are logical (conceptual) tables created by SQL Server for trigger statements. They are structurally similar to the table on which the trigger is defined and hold the old values or new values of the rows that might be changed by the user action. The tables track row-level changes in Transact-SQL. These tables provide the same functionality as Oracle row-level triggers. When an INSERT, UPDATE, or DELETE statement is executed in SQL Server, rows are added to the trigger table and to the INSERTED and DELETED table(s) simultaneously.

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, when 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. A full outer join is used to query all rows from either table.

Oracle	Microsoft 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

You can create a trigger only in the current database, though you can 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 32 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 is exceeded and the trigger is canceled. Additionally, if an update trigger on one column of a table results in an update to another column, the update trigger is activated only once.

Microsoft SQL Server declarative referential integrity (DRI) does not provide cross-database referential integrity. If cross-database referential integrity is required, use triggers.

The following statements are not allowed in a Transact-SQL trigger:

CREATE statements (DATABASE, TABLE, INDEX, PROCEDURE, DEFAULT, RULE, TRIGGER, SCHEMA, and VIEW)
DROP statements (TRIGGER, INDEX, TABLE, PROCEDURE, DATABASE, VIEW, DEFAULT, RULE)
ALTER statements (DATABASE, TABLE, VIEW, PROCEDURE, TRIGGER)
TRUNCATE TABLE
GRANT, REVOKE, DENY
UPDATE STATISTICS
RECONFIGURE
UPDATE STATISTICS
RESTORE DATABASE, RESTORE LOG
LOAD LOG, DATABASE
DISK statements
SELECT INTO (because it creates a table)

For more information about triggers, see SQL Server Books Online.