BAITAP1_DBMS

--BAITHI - ITPLANET
---------------------------
DROP DATABASE BAITHI
GO
CREATE DATABASE BAITHI
GO
USE BAITHI
GO
IF EXISTS (SELECT * FROM SysObjects WHERE name = 'LOI') DROP TABLE LOI
IF EXISTS (SELECT * FROM SysObjects WHERE name = 'DOITUONG') DROP TABLE DOITUONG
IF EXISTS (SELECT * FROM SysObjects WHERE name = 'VIPHAM') DROP TABLE VIPHAM
GO
CREATE TABLE LOI
(
MALOI CHAR(4) PRIMARY KEY,
NOIDUNG nvarchar(50),
MUCTIENPHAT money
)
GO
CREATE TABLE DOITUONG
(
MADT CHAR(4) PRIMARY KEY,
HOTEN nvarchar(50),
CMND VARCHAR(50),
DIACHI nvarchar(50),
BIENKS VARCHAR(50),
TONGTIENPHAT money
)
GO
CREATE TABLE VIPHAM
(
MALOI CHAR(4) ,
MADT CHAR(4),
THOIDIEMVP smalldatetime,
NGAYHEN smalldatetime,
PRIMARY KEY (MALOI,MADT)
)
GO
--1 . KHAI BÁO KHÓA NGOẠI CHO CÁC BẢNG
ALTER TABLE VIPHAM ADD CONSTRAINT FK_MALOI FOREIGN KEY (MALOI) REFERENCES LOI (MALOI)
ALTER TABLE VIPHAM ADD CONSTRAINT FK_MADT FOREIGN KEY (MADT) REFERENCES DOITUONG (MADT)
--2. NHẬP LIỆU CHO CÁC BẢNG
INSERT INTO LOI VALUES ('L01',N'Không đội mũ bảo hiểm','200000');
INSERT INTO LOI VALUES ('L02',N'Vượt đèn đỏ','400000');
INSERT INTO LOI VALUES ('L03',N'Chuyển hướng không có tín hiệu','300000');

INSERT INTO DOITUONG VALUES ('DT01',N'Nguyễn Văn A','272123123',N'Đồng Nai','60L1-1234','600000');
INSERT INTO DOITUONG VALUES ('DT02',N'Trần Thị B','021999777',N'TP.HCM','59T1-22.334','300000');
INSERT INTO DOITUONG VALUES ('DT03',N'Hồ Văn C','283666555',N'Bình Dương','61U2-6789','0');

INSERT INTO VIPHAM VALUES ('L01','DT01','12/27/2014','01/03/2015');
INSERT INTO VIPHAM VALUES ('L02','DT01','12/27/2014','01/06/2015');
INSERT INTO VIPHAM VALUES ('L03','DT02','01/20/2015','01/27/2015');

--3. HIỆN THỰC RÀNG BUỘC TOÀN VẸN: Tổng tiền phạt (TONGTIENPHAT) của một đối tượng là tổng mức tiền phạt (MUCTIENPHAT)
--của các lỗi mà người đó đã vi phạm.
--các thao tác gây ra lỗi
--1: thêm một đối tượng mới vào bảng DOITUONG
--2.Khi thêm một vi phạm mới vào bảng VIPHAM
--3.Khi sửa thuộc tính (TONGTIENPHAT) trong bảng DOITUONG
--4.Khi xóa quan hệ VIPHAM
--5. khi sửa thuộc tính (MUCTIENPHAT) trong bảng LOI

--1.thêm một đối tượng mới vào bảng DOITUONG
GO
CREATE TRIGGER TG_3
ON DOITUONG
FOR INSERT
AS
  BEGIN
      DECLARE @TONGTIENPHAT money

      IF((SELECT TONGTIENPHAT FROM inserted)=0)
       BEGIN
        print ('Thêm thành công')
      END
      ELSE
          ROLLBACK  TRANSACTION
   END



INSERT INTO DOITUONG VALUES ('DT05',N'Trần Thị NGọc','021999777',N'TP.HCM','59T1-22.334','0');

INSERT INTO VIPHAM VALUES ('L02','DT05','12/27/2014','02/06/2015');
INSERT INTO VIPHAM VALUES ('L03','DT05','01/20/2015','02/27/2015');
--2.Khi thêm một vi phạm mới vào bảng VIPHAM

GO

CREATE TRIGGER TG_3_2
ON VIPHAM
FOR INSERT
AS
  BEGIN
    DECLARE @MALOI CHAR(4)
    DECLARE @MADT CHAR(4)
    SELECT @MALOI=MALOI ,@MADT=MADT  FROM INSERTED
   IF(NOT EXISTS (SELECT @MALOI,@MADT
                  FROM LOI, VIPHAM, DOITUONG))
           BEGIN
             UPDATE DOITUONG
             SET TONGTIENPHAT=TONGTIENPHAT+L.MUCTIENPHAT
             FROM LOI L, DOITUONG DT
             WHERE L.MALOI=@MALOI AND DT.MADT=@MADT
            PRINT('Thêm thành công')
           END
   END

INSERT INTO VIPHAM VALUES ('L06','DT08','01/20/2015','01/27/2015');
   GO

--3.3: Update TONGTIENPHAT in DOITUONG
DROP TRIGGER TGG_3_3
GO
CREATE TRIGGER TGG_3_3
ON DOITUONG
FOR UPDATE
AS
    BEGIN
        DECLARE @MADT CHAR(4)
        SELECT @MADT=MADT FROM INSERTED
        DECLARE @TONGTIENPHAT MONEY
        SELECT @TONGTIENPHAT=TONGTIENPHAT FROM INSERTED
        IF(NOT EXISTS (SELECT * FROM VIPHAM WHERE MADT=@MADT))
            BEGIN
                RAISERROR('Đối tượng này chưa từng vi phạm, không nên vu oan như vậy =)) ', 16,1)
                ROLLBACK TRAN
            END
        DECLARE @TMP MONEY
        SELECT @TMP= SUM(MUCTIENPHAT) FROM VIPHAM, LOI WHERE VIPHAM.MADT=@MADT AND VIPHAM.MALOI=LOI.MALOI GROUP BY VIPHAM.MADT
        IF(@TONGTIENPHAT<> @TMP)
            BEGIN
            DECLARE @stmp VARCHAR(200)
            SET @stmp=CONVERT(VARCHAR(200),@TMP)
                RAISERROR('Tổng tiền phạt phải bằng %s',16,1,@stmp)
                ROLLBACK TRAN
            END
    END
GO

UPDATE DOITUONG SET TONGTIENPHAT=200000 WHERE MADT='DT04'

GO
--3.4.Khi xóa quan hệ VIPHAM

CREATE TRIGGER TG_3_4
ON VIPHAM
FOR DELETE
AS
  BEGIN
    DELETE FROM DOITUONG WHERE MADT=(SELECT MADT

                                               FROM INSERTED);
    DELETE FROM LOI WHERE MALOI=(SELECT MALOI
                                             FROM INSERTED );

  END
  GO
  DELETE FROM VIPHAM
         WHERE MALOI='L01'

  DELETE FROM VIPHAM
         WHERE MADT='DT01'
 GO

SELECT*
FROM VIPHAM

--3.5. khi sửa thuộc tính (MUCTIENPHAT) trong bảng LOI

GO
DROP TRIGGER TG_3_5
GO
CREATE TRIGGER TG_3_5
ON LOI
FOR UPDATE
AS
  BEGIN
   DECLARE @MUCTIENPHAT_CU MONEY
   DECLARE @MUCTIENPHAT_MOI MONEY
   DECLARE @MALOI CHAR(4)
   SELECT @MALOI=MALOI FROM INSERTED
   SELECT @MUCTIENPHAT_MOI=MUCTIENPHAT FROM INSERTED
   SELECT @MUCTIENPHAT_CU=MUCTIENPHAT FROM DELETED WHERE MALOI=@MALOI


   IF(@MUCTIENPHAT_MOI<>@MUCTIENPHAT_CU)
   BEGIN
        DECLARE @CHENHLECH MONEY
        SET @CHENHLECH=@MUCTIENPHAT_MOI-@MUCTIENPHAT_CU
         UPDATE DOITUONG
         SET TONGTIENPHAT=TONGTIENPHAT+@CHENHLECH
         WHERE MADT IN (SELECT MADT FROM VIPHAM WHERE MALOI=@MALOI)
     PRINT('Update thành công!!')
    END
    ELSE PRINT('GIá trị mới và giá trị cũ bằng nhau!')
END
GO
UPDATE LOI
SET MUCTIENPHAT =2000000
WHERE MALOI='L01'

GO
--Câu 4:
----Bảng tầm AH:
-----------------------------------------------------------------
---------|    INSERT    |    DELETE    |       UPDATE        |---
--+------+--------------+--------------+---------------------+---
--|  LOI |      -       |      -       |   +(MUCTIENPHAT)    |---
--+------+--------------+--------------+---------------------+---
--|VIPHAM|      +       |      -       |+(THOIDIEMVP,NGAYHEN)|---
--+------+--------------+--------------+---------------------+---
-----------------------------------------------------------------
DROP TRIGGER TGG_4_1
GO
CREATE TRIGGER TGG_4_1
ON LOI
FOR UPDATE
AS
    BEGIN
        DECLARE @MUCTIENPHAT money
        DECLARE @MALOI CHAR(4)
        SELECT @MUCTIENPHAT=MUCTIENPHAT FROM INSERTED
        SELECT @MALOI=MALOI FROM INSERTED
        IF(@MUCTIENPHAT>300000)
            BEGIN
                UPDATE VIPHAM
                SET NGAYHEN= DATEADD(DAY,10,THOIDIEMVP)
                WHERE MALOI=@MALOI AND DATEDIFF(DAY,THOIDIEMVP,NGAYHEN)<10
                PRINT('Update thành công!')
            END
        ELSE PRINT('Mức tiền phạt <300k')
    END
GO

UPDATE LOI
SET MUCTIENPHAT=500000
WHERE MALOI='L01'

GO

CREATE TRIGGER TGG_4_2
ON VIPHAM
FOR INSERT, UPDATE
AS
    BEGIN
        DECLARE @MALOI CHAR(4)
        SELECT @MALOI=MALOI FROM INSERTED
        IF(SELECT MUCTIENPHAT FROM LOI WHERE MALOI=@MALOI)>300000
            BEGIN
                IF(SELECT DATEDIFF(DAY,THOIDIEMVP,NGAYHEN) FROM INSERTED)<10
                    BEGIN
                        RAISERROR('Ngày hẹn phải sau thời điểm vi phạm ít nhất 10 ngày!',16,1)
                        ROLLBACK TRAN
                    END
            END
    END
GO
INSERT INTO VIPHAM VALUES('L01','DT03','12/27/2014','1/3/2015')