Triggers and System Stored Procedures (Level 2)
| SQL Server 6.x | SQL Server 2000 |
|---|---|
| sp_helpsql provided syntax for Transact-SQL statements, system stored procedures, and other special topics. | sp_helpsql is included, but no longer returns syntax information for Transact-SQL statements or system stored procedures. Executing sp_helpsql produces a message that recommends obtaining syntax information from Online Help.
Expect different results as compared to earlier versions of SQL Server. Use SQL Server Books Online for the syntax of Transact-SQL statements and system stored procedures. |
| Only one trigger for each data modification event (INSERT, UPDATE, or DELETE) was allowed for each table. If a new trigger was created for a specific data modification event, it replaced the previous trigger. | Microsoft® SQL Server™ allows multiple triggers to be created for each data modification event (DELETE, INSERT, or UPDATE). For example, if CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE trigger, an additional UPDATE trigger is created.
Expect different results as compared to earlier versions of SQL Server. Enable multiple triggers by setting the compatibility level to 80 in sp_dbcmptlevel. Retain SQL Server 6.x behavior by setting the compatibility level to 60 or 65. For more information, see sp_dbcmptlevel and CREATE TRIGGER. |
| If a trigger modified the table on which it was defined, the triggers were not invoked recursively for that modification. | SQL Server allows recursive invocation of triggers.
Expect different results as compared to earlier versions of SQL Server. Enable recursive triggers by setting the RECURSIVE_TRIGGERS database option. For more information about recursive and nested triggers, see Nested Triggers. |
| Several parameters of sp_create_removable referred to devices. | Devices have been replaced with files and filegroups.
Expect different results as compared to earlier versions of SQL Server. Replace all device references in sp_create_removable with references to filegroups |
| References to text or image columns in either the inserted or deleted tables appeared as NULL. | References to text or image columns in both the inserted and deleted tables are no longer allowed unless the compatibility level setting of sp_dbcmptlevel is 60 or 65.
Expect a difference in behavior when referring to text or image columns in inserted and deleted tables when using CREATE TRIGGER, depending on the setting of sp_dbcmptlevel. |
| SQL Server searched the current database followed by a search in master for a stored procedure using the sp_ prefix. | Stored procedures with the prefix sp_ are first looked up in master. If a user-defined stored procedure has the same name as a system-supplied stored procedure residing in master, SQL Server always finds the system-supplied stored procedure.
Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when calling user-defined stored procedures with the sp_ prefix. Either explicitly qualify the name of the user-defined stored procedure, or rename the user-defined stored procedure. |
| The settings of SET ANSI_NULLS and SET QUOTED_IDENTIFIER statements were active only during the session that changed either option. | The settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS are saved when a stored procedure is created or altered. These original settings are enabled when the stored procedure is executed, and any client session settings are restored afterward. Within the stored procedure, any changes to SET ANSI_NULLS do not take effect until after the stored procedure executes.
Expect different results as compared to earlier versions of SQL Server. Develop databases or applications with one setting for SET QUOTED_IDENTIFIER, SET ANSI_NULLS, and all other pertinent SET options. If a client session changes SET options, do so outside of stored procedures. |
| When executing remote stored procedures, these procedures may have assumed non-standard behavior for the options set by SET ANSI_DEFAULTS. In addition, remote stored procedures may not have explicitly set these options (set by SET ANSI_DEFAULTS) to OFF. | When executing remote stored procedures, these procedures are executed with SET ANSI_DEFAULTS set to ON.
Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when executing remote stored procedures if non-standard settings were used with SET ANSI_DEFAULTS, or if options were not explicitly set to OFF. |