Trigger, stored procedure, and stored procedure

Trigger, stored procedure, and stored procedure
Trigger

Existing dictionary table (Dict)

Field	Description
Id	Mark
ItemKey	Key
ItemValue	Value
UpperId	Upper Layer ID

Requirement 1: when a new record is added, if the same key already exists, insertion is rejected.

// Procedure: Expand the related table, right-click the 'trigger ', and create a new table.

USE [sqlffwj] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate trigger [dbo]. [CheckKeyRepeated] ON [dbo]. [Dict] for INSERTASif (select COUNT (*) from [Dict], inserted inobj where [Dict]. itemKey = inobj. itemKey and [Dict]. id! = Inobj. Id)> 0 BEGIN raiserror ('same key already exists, cannot insert ',) rollback tranEND

Requirement 2: When a record is deleted, it is rejected if a lower-level record exists.

USE [sqlffwj] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate trigger [dbo]. [CheckDependence] ON [dbo]. [Dict] for deleteASif (select COUNT (*) from [Dict], deleted delbj where [Dict]. upperId = delbj. id)> 0 BEGIN raiserror ('lower-level record exists and cannot be deleted ',) rollback tranEND

Requirement 3: When a record is deleted, if a lower-level record exists, the lower-level record is also deleted.

USE [sqlffwj]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[CheckDependence2]   ON  [dbo].[Dict]   AFTER deleteASwhile(select COUNT(*) from [Dict] where UpperId != 0 and UpperId not in (select Id from [Dict])) > 0BEGIN    delete from [Dict]     where UpperId != 0 and UpperId not in (select Id from [Dict])    END

 

Stored Procedure

Existing User table)

Field	Description
Id	Mark
Name	Name
Age	Age
DeptId	Department ID

Department table (Dept)

Field	Description
Id	Mark
Name	Name

Requirement 1: use stored procedures to query information of all users (ID, name, age, and department name)

// Operation procedure: expand the database, expand the programmability, right-click 'stored process', and create a new one.

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate procedure [dbo]. [GetUsers] ASBEGIN select [User]. id' flag ', [User]. name 'name', [User]. age 'age', [Dept]. name 'department 'from [User] left join [Dept] on [User]. deptId = [Dept]. idENDGO

/* Call */exec GetUsers

Requirement 2: Use the stored procedure to query the user information (ID, name, age, and department name) of a specified department)

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate procedure [dbo]. [GetUsersByDept] (@ deptid int/* Department ID */) ASBEGIN select [User]. id' flag ', [User]. name 'name', [User]. age 'age', [Dept]. name 'department 'from [User] left join [Dept] on [User]. deptId = [Dept]. id where [Dept]. id = @ deptidENDGO

/* Call */exec GetUsersByDept 2

Requirement 3: Call the Stored Procedure 'getusersbydept' using ADO in the project'

1. Environment: VS2010 + sql2008

2. Create an edmx file and reference two tables and stored procedures.

3. Switch to 'model browser' and 'add function import'

 

4. On the 'Add function import' panel, click 'retrieve column information'. After obtaining column information, click 'create complex type ', after confirmation, you can call the stored procedure through Func.

5. Call the code Demo

            using (var context = new SqltestEntities())             {                var result = context.GetUsersByDept(2);                throw new Exception(result.Count().ToString());            }