[13520171] DBMS_Giuaki_2014.sql

/*
	Student: Van-Duyet Le
	ID: 13520171
	Date: 10-02-2015
*/

/****** Init Database ******/

-- DROP DATABASE [DBMS_Giuaki_2014]
-- CREATE DATABASE [DBMS_Giuaki_2014]
-- USE [DBMS_Giuaki_2014]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Table [dbo].[KYNANG]    Script Date: 02/10/2015 20:21:56 ******/

CREATE TABLE [dbo].[KYNANG](
	[MAKN] [int] NOT NULL,
	[TENKN] [nvarchar](50) NULL,
 CONSTRAINT [PK_KYNANG] PRIMARY KEY CLUSTERED 
(
	[MAKN] 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


/****** Object:  Table [dbo].[NHANVIEN]    Script Date: 02/10/2015 20:22:08 ******/

CREATE TABLE [dbo].[NHANVIEN](
	[MANV] [int] NOT NULL,
	[HOTEN] [nvarchar](50) NULL,
	[NGSINH] [smalldatetime] NULL,
	[NGVL] [smalldatetime] NULL,
	[MUCLUONG] [float] NULL,
	[TRINHDO] [int] 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


/****** Object:  Table [dbo].[PHONGBAN]    Script Date: 02/10/2015 20:22:23 ******/

CREATE TABLE [dbo].[PHONGBAN](
	[MAPB] [int] NOT NULL,
	[TENPB] [nvarchar](50) NULL,
 CONSTRAINT [PK_PHONGBAN] PRIMARY KEY CLUSTERED 
(
	[MAPB] 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


/****** Object:  Table [dbo].[NHANVIEN_KYNANG]    Script Date: 02/10/2015 20:22:36 ******/

CREATE TABLE [dbo].[NHANVIEN_KYNANG](
	[MANV] [int] NOT NULL,
	[MAKN] [int] NOT NULL,
	[NGDK] [smalldatetime] NULL,
	[GHICHU] [nvarchar](50) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[NHANVIEN_KYNANG]  WITH CHECK ADD  CONSTRAINT [FK_NHANVIEN_KYNANG_KYNANG] FOREIGN KEY([MAKN])
REFERENCES [dbo].[KYNANG] ([MAKN])
GO

ALTER TABLE [dbo].[NHANVIEN_KYNANG] CHECK CONSTRAINT [FK_NHANVIEN_KYNANG_KYNANG]
GO


/************************************/
-- Question a
DROP PROCEDURE PROC_CREATE_NEW_STAFF
GO 

CREATE PROCEDURE PROC_CREATE_NEW_STAFF (
	@hoten nvarchar(50), 
	@ngsinh smalldatetime,
	@ngvl smalldatetime, 
	@mucluong float,
	@trinhdo int
)
AS
	BEGIN
		DECLARE @last_id int = 0;
		
		IF (SELECT COUNT(*) FROM [dbo].[NHANVIEN]) > 0 
			BEGIN

				DECLARE @current_id int;
				
				DECLARE Staff_Cursor_PROC_CREATE_NEW_STAFF CURSOR FOR SELECT MANV FROM [dbo].[NHANVIEN];
				OPEN Staff_Cursor_PROC_CREATE_NEW_STAFF;
				FETCH NEXT FROM Staff_Cursor_PROC_CREATE_NEW_STAFF INTO @current_id;
				
				WHILE @@FETCH_STATUS = 0
					BEGIN
						SET @last_id = @last_id + 1;
						
						IF (@last_id = @current_id)
							FETCH NEXT FROM Staff_Cursor_PROC_CREATE_NEW_STAFF INTO @current_id;
						ELSE BREAK;
					END
					
				CLOSE Staff_Cursor_PROC_CREATE_NEW_STAFF;
				DEALLOCATE Staff_Cursor_PROC_CREATE_NEW_STAFF;
				
				IF @last_id = @current_id SET @last_id += 1;		
			END
		ELSE SET @last_id = 1; -- IF TABLE IS EMPTY
		
		
		INSERT INTO [dbo].[NHANVIEN]
				   ([MANV], [HOTEN], [NGSINH], [NGVL], [MUCLUONG], [TRINHDO])
			 VALUES
				   (@last_id, @hoten, @ngsinh, @ngvl, @mucluong, @trinhdo)
	
	END


-- Test procedure
	-- Insert 4 Member
	EXEC PROC_CREATE_NEW_STAFF 'Van-Duyet Le', '1/1/1995', '2/2/2022', 1560000, 1;
	EXEC PROC_CREATE_NEW_STAFF 'Van-Duyet Le', '1/1/1995', '2/2/2022', 1560000, 1;
	EXEC PROC_CREATE_NEW_STAFF 'Van-Duyet Le', '1/1/1995', '2/2/2022', 1560000, 1;
	EXEC PROC_CREATE_NEW_STAFF 'Van-Duyet Le', '1/1/1995', '2/2/2022', 1560000, 1;

	-- Look at the table 
	SELECT * FROM [dbo].[NHANVIEN]

	-- Drop user 2
	DELETE FROM [dbo].[NHANVIEN] WHERE MANV = 2 
	
	-- Relook the table, its have 3 member (1,3,4)
	SELECT * FROM [dbo].[NHANVIEN]
	
	-- Try again
	EXEC PROC_CREATE_NEW_STAFF 'Van-Duyet Le', '1/1/1995', '2/2/2000', 1560000, 1;
	
	-- See more and you have 4 member
	SELECT * FROM [dbo].[NHANVIEN]

/************************************/
-- Question b
DROP PROCEDURE PROC_UPDATE_STAFF_LEVEL
GO 

CREATE PROCEDURE PROC_UPDATE_STAFF_LEVEL 
AS 
	BEGIN
		DECLARE @manv int, @ngvl smalldatetime, @trinhdo int, @updated_count int = 0;
		DECLARE Staff_Cursor_PROC_UPDATE_STAFF_LEVEL CURSOR FOR SELECT [MANV], [NGVL], [TRINHDO] FROM [dbo].[NHANVIEN];
		OPEN Staff_Cursor_PROC_UPDATE_STAFF_LEVEL;
		
		FETCH NEXT FROM Staff_Cursor_PROC_UPDATE_STAFF_LEVEL INTO @manv, @ngvl, @trinhdo;
				
		WHILE @@FETCH_STATUS = 0
			BEGIN
				IF (@trinhdo = 1 AND DATEADD(DAY, -365*3, GETDATE()) > @ngvl) 
					BEGIN
						UPDATE [dbo].[NHANVIEN] SET TRINHDO = 2 WHERE MANV = @manv;
						SET @updated_count += 1;
					END
				
				FETCH NEXT FROM Staff_Cursor_PROC_UPDATE_STAFF_LEVEL INTO @manv, @ngvl, @trinhdo;
			END
			
		CLOSE Staff_Cursor_PROC_UPDATE_STAFF_LEVEL;
		DEALLOCATE Staff_Cursor_PROC_UPDATE_STAFF_LEVEL;
		
		PRINT 'Update Staff Level: '+ CONVERT(varchar, @updated_count) +' row(s) affected';
	END

-- Test it
EXEC PROC_UPDATE_STAFF_LEVEL
SELECT * FROM [dbo].[NHANVIEN]


/************************************/
-- Question c
DROP TRIGGER TRIGGER_CHECK_SALARY_LEVEL 
GO

CREATE TRIGGER TRIGGER_CHECK_SALARY_LEVEL
ON [dbo].[NHANVIEN]
FOR INSERT, UPDATE
AS 
	BEGIN
		IF (SELECT COUNT(*) 
			FROM INSERTED 
			WHERE [TRINHDO] = 1 AND MUCLUONG > 10000000) > 0
		BEGIN
			RAISERROR ('With Staff level is 1, the salary must be < 10.000.00', 17,0);
			ROLLBACK TRANSACTION
		END
	END
	
-- TEST DATA
EXEC PROC_CREATE_NEW_STAFF 'Duyet', '1/1/1995', '2/2/2022', 1500000000, 1;