|
|
Error Message
Server: Msg 160, Level 15, State 1,
Procedure <Rule Name>, Line 1
Rule does not contain a variable.
Causes
Rules are a backward-compatibility feature that performs some of the same functions as CHECK constraints. When a rule is bound to a column or an alias data type, the rule specifies the acceptable values that can be inserted into that column.
|
The syntax of the CREATE RULE is as follows:
CREATE RULE <Rule Name>
AS <Condition Expression>
The <Condition Expression> is the condition or conditions that define the rule. One of the requirements of the <Condition Expression> is that it should include one variable, which is preceded by the at sign (@). If the <Condition Expression> does not have a local variable, this error message is encountered.
Here’s an example of how the error can occur:
CREATE RULE [AdministratorOnlyRule]
AS SUSER_NAME() = 'sysadmin'
Msg 160, Level 15, State 1, Procedure AdministratorOnlyRule, Line 1
Rule does not contain a variable.
This rule tries to limit the users that can perform updates or inserts to the table. If the user is not “sysadmin”, it will supposedly reject the UPDATE or INSERT request.
Here’s another example of a rule definition that will generate this error:
CREATE RULE [TestServerRule]
AS SERVERPROPERTY('servername') = 'SQLTestServer'
Msg 160, Level 15, State 1, Procedure TestServerRule, Line 1
Rule does not contain a variable.
This rule tries to allow updates or inserts to the table only if the statement is executed on the server “SQLTestServer”.
CREATE RULE will be removed in a future version of Microsoft SQL Server. Avoid using CREATE RULE and instead make use of CHECK constraints instead. Using the CHECK constraint is the preferred, standard way to restrict the values in a column. There can be only one rule applied to a column, but multiple CHECK constraints can be applied. CHECK constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE.
Solution / Work Around:
Depending on the requirement, a rule can either be replaced by a CHECK constraint or by a trigger. In this particular error message, since the rule does not involve any column in the table, the rule has to be replaced with a trigger.
In the case of the first rule where it checks if the current user is “sysadmin”, this rule can be replaced with the following trigger:
CREATE TRIGGER [dbo].[AdministratorOnly]
ON [dbo].[Customers]
FOR INSERT, UPDATE, DELETE
AS
IF SUSER_NAME() != 'sysadmin'
BEGIN
RAISERROR ('Only sysadmin can insert, update or delete from this table.', 16, 1)
ROLLBACK TRANSACTION
END
Inserting a row in the Customers table with this trigger enabled will raise an error if the user is not “sysadmin”:
INSERT INTO [dbo].[Customers] ( [FirstName], [LastName], [MaritalStatus] )
VALUES ( 'Mickey', 'Mouse', 'M')
Msg 50000, Level 16, State 1, Procedure AdministratorOnly, Line 1
Only sysadmin can insert, update or delete from this table.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
In the case of the second rule where it checks if the server is “SQLTestServer”, the following trigger can take its place
CREATE TRIGGER [dbo].[TestServerOnly]
ON [dbo].[Employee]
FOR INSERT, UPDATE, DELETE
AS
IF SERVERPROPERTY('servername') != 'SQLTestServer'
BEGIN
RAISERROR ('Updates on this table can only be done on the test server.', 16, 1)
ROLLBACK TRANSACTION
END
Inserting a new employee to the Employees table with this trigger enabled will raise an error if the name of the server is not “SQLTestServer”.
INSERT INTO [dbo].[Employees] ( [FirstName], [LastName] )
VALUES ( 'Donald', 'Duck' )
Msg 50000, Level 16, State 1, Procedure TestServerOnly, Line 7
Updates on this table can only be done on the test server.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
|