Presents your 
SQL SERVER E-NEWSLETTER for March 18, 2003

<------------------------------------------->

ENSURE MODIFICATION BY A SPECIFIC APPLICATION

For inserts and updates, a table-level constraint can be used to ensure
that a modification is being made by a specific application. For deletes
to the table, a trigger can be used. For both types of constraints, the
function APP_NAME () can be used to validate the proper application to 
use in updating the data.

Table-level check constraints are fired before and after a trigger is
fired. Since a check constraint is validating data as it's being introduced
to the table, it cannot validate a delete operation. An after trigger,
on the other hand, can validate the delete operation.

A column- or table-level check constraint uses minimal code to expedite
the requirement of allowing only a specific application to update a table.

The following is sample code that creates a table called CheckAppName. 
A table-level check constraint is added to check for an application,
VBApp. An after delete trigger is created to check for the same 
application as in the check constraint, VBApp.

USE Northwind
GO
SET NOCOUNT ON
GO
CREATE TABLE CheckAppName
(CheckAppNameId INT NOT NULL
     CONSTRAINT PkCheckAppName PRIMARY KEY CLUSTERED,
  DescrText char (20) NOT NULL,
 CONSTRAINT tck_AppName CHECK (APP_NAME() = 'VBApp'))
GO
-- ==============================================
-- Create trigger basic template(After triggger)
-- ==============================================
IF EXISTS (SELECT name
      FROM   sysobjects
      WHERE  name = N'tiud_CheckAppName'
      AND      type = 'TR')
    DROP TRIGGER tiud_CheckAppName
GO
CREATE TRIGGER tiud_CheckAppName
ON Northwind.dbo.CheckAppName
FOR DELETE
AS
BEGIN
   IF (SELECT APP_NAME()) != 'VBApp'
   BEGIN
    RAISERROR ('CAN NOT USE THIS APPLICATION TO UPDATE TABLE ', 16, 10)
    ROLLBACK
   END
END
GO

The following sample code is trying to insert values, and then delete
rows from the table using SQL Query Analyzer. SQL Query Analyzer is 
also
the application name that the function APP_NAME () will retrieve.

INSERT Northwind.dbo.CheckAppName VALUES (1, 'TEST')
GO
SELECT * FROM Northwind.dbo.CheckAppName
GO
DELETE Northwind.dbo.CheckAppName
GO
SELECT * FROM Northwind.dbo.CheckAppName
GO

The insert and delete should fail because of the check constraint. The
delete should fail due to the trigger error message.

The following sample code is trying to update the table. The check
constraint error will not be issued since there is no data for the 
update
statement to execute against. The code will then disable both the 
trigger and
the check constraint.

Once the trigger and the check constraint are disabled, a row is
inserted into the table. Then the trigger and check constraint are 
re-enabled.

UPDATE Northwind.dbo.CheckAppName SET DescrText = 'Test 3.1'
GO
SELECT * FROM Northwind.dbo.CheckAppName
GO
ALTER TABLE Northwind.dbo.CheckAppName DISABLE TRIGGER 
tiud_CheckAppName
GO
ALTER TABLE Northwind.dbo.CheckAppName NOCHECK CONSTRAINT tck_AppName
GO
INSERT Northwind.dbo.CheckAppName VALUES (1, 'TEST')
GO
SELECT * FROM Northwind.dbo.CheckAppName
GO
ALTER TABLE Northwind.dbo.CheckAppName ENABLE TRIGGER tiud_CheckAppName
GO
ALTER TABLE Northwind.dbo.CheckAppName CHECK CONSTRAINT tck_AppName
GO
UPDATE Northwind.dbo.CheckAppName SET DescrText = 'Test 3.1'
GO
SELECT * FROM Northwind.dbo.CheckAppName
GO
DROP TABLE Northwind.dbo.CheckAppName
GO

This time, the check constraint error should be raised when the update
statement is executed.

----------------------------------------