DATA_DEPENDENCY.sql

USE [master]
GO
/****** Object:  Database [DATA_DEPENDENCY]    Script Date: 18-May-22 7:00:02 AM ******/
CREATE DATABASE [DATA_DEPENDENCY]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DATA_DEPENDENCY', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DATA_DEPENDENCY.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'DATA_DEPENDENCY_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DATA_DEPENDENCY_log.ldf' , SIZE = 401408KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [DATA_DEPENDENCY] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DATA_DEPENDENCY].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [DATA_DEPENDENCY] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET ARITHABORT OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET  ENABLE_BROKER 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET RECOVERY FULL 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET  MULTI_USER 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [DATA_DEPENDENCY] SET DB_CHAINING OFF 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [DATA_DEPENDENCY] SET DELAYED_DURABILITY = DISABLED 
GO
EXEC sys.sp_db_vardecimal_storage_format N'DATA_DEPENDENCY', N'ON'
GO
ALTER DATABASE [DATA_DEPENDENCY] SET QUERY_STORE = OFF
GO
USE [DATA_DEPENDENCY]
GO
/****** Object:  Table [dbo].[NHANVIEN]    Script Date: 18-May-22 7:00:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NHANVIEN](
	[MANV] [int] IDENTITY(1,1) NOT NULL,
	[HO] [nvarchar](50) NULL,
	[TEN] [nvarchar](10) NULL,
	[PHAI] [nvarchar](5) NULL,
	[DIACHI] [nvarchar](200) NULL,
	[NGAYSINH] [date] NULL,
	[LUONG] [float] NULL,
 CONSTRAINT [PK_NHANVIEN] PRIMARY KEY CLUSTERED 
(
	[MANV] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[NHANVIEN] ON 

INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (1, N'Nguyễn Thanh', N'Sơn', N'Nam', N'Quận Bình Thạnh', CAST(N'1999-02-12' AS Date), 2000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (2, N'Vũ Nguyễn Quỳnh', N'Như', N'Nữ', N'Quận Tân Phú', CAST(N'2000-03-01' AS Date), 1000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (5, N'LÊ THỊ HƯƠNG', N'GIANG', N'NỮ', N'Quận 4', CAST(N'2002-04-06' AS Date), 800)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (6, N'Nguyễn', N'Linh', N'Nữ', N'Quận Thủ Đức', CAST(N'1999-01-01' AS Date), 1400)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (7, N'Lê văn', N'Hậu', N'Nam', N'Hà Nội', CAST(N'1999-04-29' AS Date), 1000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (8, N'Trần Nguyên', N'Khang', N'Nam', N'Long An', CAST(N'1999-04-07' AS Date), 3000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (9, N'Lê Thị', N'Giang', N'Nữ', N'Biên Hòa', CAST(N'1990-09-07' AS Date), 100)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (10, N'Trần Thị', N'Lan', N'Nữ', N'Quận Tân Phú', CAST(N'1999-04-02' AS Date), 2500)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (11, N'Lê Minh', N'Tâm', N'Nam', N'Nam Định', CAST(N'1999-04-03' AS Date), 2000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (12, N'Lê Công', N'Trí', N'Nam', N'Định Quán', CAST(N'1999-04-03' AS Date), 200)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (13, N'Lê Nam', N'Phát', N'Nam', N'Bình Dương', CAST(N'1999-04-07' AS Date), 1000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (15, N'Nguyễn Văn', N'Toàn', N'Nam', N'Quận 2', CAST(N'1998-04-04' AS Date), 1000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (16, N'Võ Thị', N'Minh', N'Nữ', N'Quận 1', CAST(N'1997-04-04' AS Date), 1300)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (17, N'Đỗ Linh', N'Chi', N'Nữ', N'Quận Phú Nhuận', CAST(N'1996-04-04' AS Date), 2500)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (20, N'Trần', N'Bảo', N'Nam', N'Quận Hoàn Kiếm, Hà Nội', CAST(N'2000-04-03' AS Date), 2000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (23, N'Lê', N'Hiếu', N'Nam', N'Vũng Tàu', CAST(N'1998-05-12' AS Date), 2000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (24, N'Nguyễn', N'Lan', N'Nữ', N'Quảng Nam', CAST(N'1998-05-18' AS Date), 1400)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (25, N'Lê thị', N'Bình', N'Nữ', N'Hoàn Kiếm, Hà Nội', CAST(N'1997-05-11' AS Date), 1500)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (26, N'Nguyễn', N'Hoa', N'Nữ', N'Gò Vấp', CAST(N'1999-05-14' AS Date), 1000)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (27, N'Lê', N'Trang', N'Nữ', N'Quận 4', CAST(N'2000-04-05' AS Date), 200)
INSERT [dbo].[NHANVIEN] ([MANV], [HO], [TEN], [PHAI], [DIACHI], [NGAYSINH], [LUONG]) VALUES (28, N'Xuân', N'Sang', N'Nam', N'Phú Nhuận', CAST(N'1999-05-19' AS Date), 1000)
SET IDENTITY_INSERT [dbo].[NHANVIEN] OFF
GO
ALTER TABLE [dbo].[NHANVIEN] ADD  CONSTRAINT [DF_NHANVIEN_PHAI]  DEFAULT (N'Nam') FOR [PHAI]
GO
ALTER TABLE [dbo].[NHANVIEN]  WITH CHECK ADD  CONSTRAINT [CK_PHAI] CHECK  (([PHAI]=N'Nữ' OR [PHAI]='Nam'))
GO
ALTER TABLE [dbo].[NHANVIEN] CHECK CONSTRAINT [CK_PHAI]
GO
/****** Object:  StoredProcedure [dbo].[SP_DEL]    Script Date: 18-May-22 7:00:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[SP_DEL] @ID int
as
begin
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	begin tran
		delete from NHANVIEN where MANV = @ID
		waitfor delay '00:00:10'
	commit
end
GO
/****** Object:  StoredProcedure [dbo].[SP_GETID]    Script Date: 18-May-22 7:00:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_GETID]
as
BEGIN
	if(select max(MANV)+1 as StaffID from NHANVIEN) is null
	select 1 as StaffID
	else select max(MANV)+1 as StaffID from NHANVIEN

	--SELECT
	--CASE
    --WHEN (SELECT COUNT(MANV) FROM NHANVIEN) = 0
       --THEN 1
    --ELSE IDENT_CURRENT('NHANVIEN') + 1
  --END AS Current_Identity;

END
GO
/****** Object:  StoredProcedure [dbo].[SP_MERGE]    Script Date: 18-May-22 7:00:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_MERGE] @ID int, @HO NVARCHAR(50), @TEN NVARCHAR(50), @PHAI NVARCHAR(5),
	@DIACHI NVARCHAR(200), @NGAYSINH DATE, @LUONG FLOAT
AS
BEGIN
	-- SET ISOLATION LEVEL --
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRAN
		MERGE INTO dbo.NhanVien AS TARGET
		USING (SELECT  MANV=@ID,  HO=@HO, TEN=@TEN, PHAI=@PHAI, DIACHI=@DIACHI, NGAYSINH=@NGAYSINH,
		LUONG=@LUONG) AS Source
		ON Target.MANV= Source.MANV
		WHEN MATCHED THEN
		UPDATE 
		SET TARGET.HO=@HO, TARGET.TEN = @TEN, TARGET.PHAI = @PHAI, TARGET.DIACHI = @DIACHI, 
		TARGET.NGAYSINH = @NGAYSINH, TARGET.LUONG = @LUONG
		WHEN NOT MATCHED THEN
		INSERT (HO, TEN, PHAI, DIACHI, NGAYSINH, LUONG)
		VALUES (Source.HO, Source.TEN, Source.PHAI, Source.DIACHI, Source.NGAYSINH, 
		Source.Luong);

		-- WAIT TO A COMMIT --
		WAITFOR DELAY '00:00:10'
	-- ROLLBACK --
	COMMIT
END
GO
/****** Object:  StoredProcedure [dbo].[SP_SELECT_DELAY]    Script Date: 18-May-22 7:00:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_SELECT_DELAY] 
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRAN
	SELECT * FROM dbo.NHANVIEN
	WAITFOR DELAY '00:00:10' --wait for 10 seconds
	COMMIT
END
GO
/****** Object:  StoredProcedure [dbo].[SP_WRITE]    Script Date: 18-May-22 7:00:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_WRITE] @ID int, @HO NVARCHAR(50), @TEN NVARCHAR(50), @PHAI NVARCHAR(5),
	@DIACHI NVARCHAR(200), @NGAYSINH DATE, @LUONG FLOAT 
AS
BEGIN
	-- SET ISOLATION LEVEL --
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRAN
	
	-- CHECK WHETHER ID HAS ALREADY EXISTED
	DECLARE @CHECKID BIT
	SET @CHECKID = 0

	IF EXISTS(SELECT MANV FROM NHANVIEN WHERE MANV = @ID)
	BEGIN
		SET @CHECKID = 1
	END
	
	-- IF ID HAS ALREADY EXISTED THEN EXECUTE UPDATING --
	-- IF ID HAS NOT ALREADY EXISTED THEN EXECUTE INSERTING --
	IF @CHECKID = 1
	BEGIN
		UPDATE NHANVIEN SET HO=@HO, TEN = @TEN, PHAI = @PHAI, DIACHI = @DIACHI, NGAYSINH = @NGAYSINH,
			LUONG = @LUONG WHERE MANV = @ID
	END
	
	ELSE
	BEGIN
		INSERT INTO NHANVIEN VALUES (@HO, @TEN, @PHAI, @DIACHI, @NGAYSINH, @LUONG)
	END

	-- WAIT -- 
	WAITFOR DELAY '00:00:30'

	COMMIT
END
GO
USE [master]
GO
ALTER DATABASE [DATA_DEPENDENCY] SET  READ_WRITE 
GO