CREATE TABLE YGB (
ID INT NOT NULL identity (1,1) PRIMARY KEY,
NAME VARCHAR(20),
LEADERLEVEL INTEGER,
LEADE_NAME VARCHAR(20),
position varchar(20))
SELECT * FROM YGB
INSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position)
VALUES('张三',2,'','总经理')
,('李四',1,'张三','部门经理')
,('王五',1,'张三','部门经理')
,('赵六',0,'李四','职员')
,('周七',0,'李四','见习职员')
,('何八',0,'王五','见习职员')
TRUNCATE TABLE YGB
GO
ALTER TRIGGER YGB_SAFETY ON YGB
Instead of INSERT
AS
BEGIN
DECLARE @NUM INT;
BEGIN
SET NOCOUNT ON;
SELECT *,CASE WHEN ISNULL((SELECT SUM(cnt) FROM (SELECT COUNT(*) AS cnt FROM YGB b WHERE a.LEADE_NAME = b.LEADE_NAME UNION ALL SELECT COUNT(*) FROM INSERTED c WHERE a.LEADE_NAME = c.LEADE_NAME )x),0)>3 THEN 0 ELSE 1 END isok
INTO #tmp_check FROM INSERTED a
INSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position)
SELECT NAME,LEADERLEVEL,LEADE_NAME,POSITION FROM #tmp_check WHERE isok = 1
END;
END ;
iNSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position) VALUES
('何久',3,'李四','见习职员')
,('何久1',3,'李四','见习职员')
GO
ALTER FUNCTION [dbo].[FIND_LEADER] (@NAME VARCHAR(20))
RETURNS @re TABLE(
id int IDENTITY(1,1),LEADE_NAME VARCHAR(20), LEADERLEVEL1 varchar(10),position1 varchar(20))
AS
BEGIN
WITH emp AS (
SELECT b.NAME,b.LEADERLEVEL,b.position FROM YGB a JOIN YGB b ON a.LEADE_NAME = b.name WHERE a.NAME='周七' UNION ALL
SELECT c.NAME,c.LEADERLEVEL,c.position FROM emp a JOIN YGB b ON a.NAME = b.name JOIN YGB c ON b.LEADE_NAME = c.name
)
INSERT into @re(LEADE_NAME,LEADERLEVEL1,position1)
SELECT * FROM emp
RETURN
END
SELECT * FROM dbo.FIND_LEADER('周七')
ID INT NOT NULL identity (1,1) PRIMARY KEY,
NAME VARCHAR(20),
LEADERLEVEL INTEGER,
LEADE_NAME VARCHAR(20),
position varchar(20))
SELECT * FROM YGB
INSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position)
VALUES('张三',2,'','总经理')
,('李四',1,'张三','部门经理')
,('王五',1,'张三','部门经理')
,('赵六',0,'李四','职员')
,('周七',0,'李四','见习职员')
,('何八',0,'王五','见习职员')
TRUNCATE TABLE YGB
GO
ALTER TRIGGER YGB_SAFETY ON YGB
Instead of INSERT
AS
BEGIN
DECLARE @NUM INT;
BEGIN
SET NOCOUNT ON;
SELECT *,CASE WHEN ISNULL((SELECT SUM(cnt) FROM (SELECT COUNT(*) AS cnt FROM YGB b WHERE a.LEADE_NAME = b.LEADE_NAME UNION ALL SELECT COUNT(*) FROM INSERTED c WHERE a.LEADE_NAME = c.LEADE_NAME )x),0)>3 THEN 0 ELSE 1 END isok
INTO #tmp_check FROM INSERTED a
INSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position)
SELECT NAME,LEADERLEVEL,LEADE_NAME,POSITION FROM #tmp_check WHERE isok = 1
END;
END ;
iNSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position) VALUES
('何久',3,'李四','见习职员')
,('何久1',3,'李四','见习职员')
GO
ALTER FUNCTION [dbo].[FIND_LEADER] (@NAME VARCHAR(20))
RETURNS @re TABLE(
id int IDENTITY(1,1),LEADE_NAME VARCHAR(20), LEADERLEVEL1 varchar(10),position1 varchar(20))
AS
BEGIN
WITH emp AS (
SELECT b.NAME,b.LEADERLEVEL,b.position FROM YGB a JOIN YGB b ON a.LEADE_NAME = b.name WHERE a.NAME='周七' UNION ALL
SELECT c.NAME,c.LEADERLEVEL,c.position FROM emp a JOIN YGB b ON a.NAME = b.name JOIN YGB c ON b.LEADE_NAME = c.name
)
INSERT into @re(LEADE_NAME,LEADERLEVEL1,position1)
SELECT * FROM emp
RETURN
END
SELECT * FROM dbo.FIND_LEADER('周七')
解决方案 »
- 现在有一个.net的系统,可以和Discuz(php)整合么?
- 关于静态页
- 关于TreeView,
- 请教asp.net下的Ajax技术目前发展到什么地步了?实现ajax的主流方式是什么呢?
- 紧急求助!
- 谁能讲讲,虚拟主机/url重写/二级域名,三个的联系和区别?
- 我想问一个关于页面生成的问题
- 紧急求救:自动生成的DataGrid的问题,谢谢!!!
- 什么实现点击树节点提交树页面中的表单呢,急
- 请问怎么连接DBF数据?(全部分都奉上)
- kestrel-hellomvc.service: Failed at step USER spawning /usr/bin/dotnet: No such
- 二级联动怎么控制gridview数据显示?
USE [Test]
GO/****** Object: Table [dbo].[YGB] Script Date: 4/25/2017 9:28:26 AM ******/
DROP TABLE [dbo].[YGB]
GO/****** Object: Table [dbo].[YGB] Script Date: 4/25/2017 9:28:26 AM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[YGB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](20) NULL,
[LEADERLEVEL] [int] NULL,
[LEADE_NAME] [varchar](20) NULL,
[position] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOSET ANSI_PADDING OFF
GOCREATE TRIGGER YGB_SAFETY ON YGB
Instead of INSERT
AS
BEGIN
DECLARE @NUM INT;
BEGIN
SET NOCOUNT ON;
SELECT *,CASE WHEN ISNULL((SELECT SUM(cnt) FROM (SELECT COUNT(*) AS cnt FROM YGB b WHERE a.LEADE_NAME = b.LEADE_NAME UNION ALL SELECT COUNT(*) FROM INSERTED c WHERE a.LEADE_NAME = c.LEADE_NAME )x),0)>3 THEN 0 ELSE 1 END isok
INTO #tmp_check FROM INSERTED a
INSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position)
SELECT NAME,LEADERLEVEL,LEADE_NAME,POSITION FROM #tmp_check WHERE isok = 1
END;
END ;
GOINSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position)
VALUES('张三',0,'','总经理')
,('李四',1,'张三','部门经理')
,('王五',1,'张三','部门经理')
,('赵六',2,'李四','职员')
,('周七',2,'李四','见习职员')
,('何八',2,'王五','见习职员') GO
DROP FUNCTION [dbo].[FIND_LEADER]
GOCREATE FUNCTION [dbo].[FIND_LEADER] (@NAME VARCHAR(20))
RETURNS @re TABLE(
id int,LEADE_NAME VARCHAR(20), LEADERLEVEL1 varchar(10),position1 varchar(20))
AS BEGIN
INSERT INTO @re
SELECT leader.ID, leader.NAME, leader.LEADERLEVEL, leader.position FROM YGB
INNER JOIN YGB leader ON YGB.LEADE_NAME = leader.NAME
where YGB.NAME = @NAME
RETURN
END
GO