ADB[7.11.18] Trigger

TRIGGER [USE FOR audit WORK]
** USE HR Database Employee



CREATE DATABASE HR;
USE HR;
CREATE TABLE employees (
E_ID VARCHAR(12) NOT NULL PRIMARY KEY,
E_Name VARCHAR(25) NOT NULL,
E_Address VARCHAR(55),
Salary INT NOT NULL,
Department VARCHAR(12)
)
INSERT INTO employees
(E_id, E_Name, E_Address, Salary, Department) VALUES
('E011','Rakib','Cumilla',64587,'HR'),
('E012','Rakib','Dhaka',24587,'IT'),
('E013','Rakiba','Dhaka',34587,'IT'),
('E014','Rasel','Chittagong',54587,'Management'),
('E015','Robin','Chadpur',24587,'Sale'),
('E016','Rubi','Dhaka',14587,'Sale'),
('E017','Kabir','Chadpur',28587,'Management'),
('E018','Rothi','Dhaka',28587,'HR');


/*Show massage when a user want to create a table*/

CREATE TRIGGER Hello
ON database
FOR create_table
AS
BEGIN

print('hey! you crate a new table ')
END
CREATE TABLE test(
id int primary key,
name VARCHAR(25)
)

CREATE TRIGGER DropTable
ON database
FOR drop_table
AS
BEGIN

print('hey! you delete a new table ')
END
DROP TABLE test;


CREATE TABLE Department (
D_id INT NOT NULL PRIMARY KEY,
D_Name VARCHAR(25) NOT NULL,
Location VARCHAR(55)
);

INSERT INTO Department (D_ID, D_Name, Location) VALUES
(11, 'BBA','Shubanbag'),
(15, 'CSE', 'Shukrabad'),
(35, 'SWE', 'Shukrabad'),
(25,'EEE', 'Shubanbag');


/**/
CREATE TABLE backup_value(
id int identity(1,1) primary key,
AuditData nvarchar(1000)
)
CREATE TRIGGER insert_data_dept
ON Department
FOR INSERT
AS
BEGIN
    DECLARE @id int
    DECLARE @name VARCHAR(20)
    DECLARE @location VARCHAR(30)

    SELECT @id = D_ID FROM inserted
    SELECT @name = D_Name FROM inserted
    SELECT @location = Location FROM inserted

    INSERT INTO backup_value VALUES(
    'new Value is '+CAST(@id AS VARCHAR(20))+
    'Name is '+@name+' Location '+@location+
    'Time is '+CAST(GETDATE() AS VARCHAR(50))
    )


END

INSERT INTO Department (D_ID, D_Name, Location) VALUES
(111, 'BBA','Shubanbag')

SELECT * FROM backup_value


/*Delete table row*/
CREATE TABLE deleted_backup_value(
id int identity(1,1) primary key,
AuditData nvarchar(1000)
)

ALTER TRIGGER delete_data_dept
ON Department
FOR DELETE
AS
BEGIN
    DECLARE @id int
    DECLARE @name VARCHAR(20)
    DECLARE @location VARCHAR(30)

    SELECT @id = D_ID FROM deleted
    SELECT @name = D_Name FROM deleted
    SELECT @location = Location FROM deleted

    INSERT INTO backup_value VALUES(
    'new Value is '+CAST(@id AS VARCHAR(20))+
    'Name is '+@name+' Location '+@location+
    'Time is '+CAST(GETDATE() AS VARCHAR(50))
    )
END

DELETE FROM Department WHERE D_id=15
 SELECT * FROM backup_value;