Create database CompanyDb
use CompanyDb

create table department
(
id int primary key,
department_name varchar(100)
)

insert into department values
(1, 'Computer science'),
(2, 'IT'),
(3, 'HR'),
(4, 'Finance'),
(5, 'Training'),
(6, 'Placement')

create table employeerecords
(
id int primary key,
Name varchar(100),
Email varchar(100),
Department_id int references department (id)
)

insert into employeerecords values
(1, 'Jitendra Maurya', 'Jitendra@gmail.com',2),
(2,'Aman', 'Aman@gmail.com',2),
(3, 'utkarsh','utkarsh@gmail.com',4),
(4, 'Hemant', 'Hemant@gmail.com', 5),
(5, 'Vinit', 'vinit@gmail.com',5),
(6,'kshama', 'kshama@gmail.com',2),
(7, 'oshima','oshima@gmail.com',1),
(8, 'Pragati', 'Pragati@gmail.com', 2),
(9, 'Rashmi', 'Rashmi@gmail.com', null),
(10, 'Priyanka','Priyanka@gmail.com', null)

create table employeerecords1
(
id int primary key,
Name varchar(100),
Email varchar(100),
Department_id int references department (id)
)
insert into employeerecords1 values
(1, 'Jitendra Maurya', 'Jitendra@gmail.com',2),
(2,'Aman', 'Aman@gmail.com',2),
(3, 'utkarsh','utkarsh@gmail.com',4),
(4, 'Hemant', 'Hemant@gmail.com', 5),
(5, 'Vinit', 'vinit@gmail.com',5),
(6,'Sristi', 'sristi@gmail.com',2),
(7, 'shalini','shalini@gmail.com',1),
(8, 'anjali', 'anjali@gmail.com', 2),
(9, 'kesav', 'keshav@gmail.com', null),
(10, 'kartikey','kartikey@gmail.com', null)

select * from employeerecords
select * from employeerecords1
select * from department






create table tbl_log(
id int identity(1,1) primary key,
logdata varchar(250)
)

select * from employeerecords
select * from tbl_log


create trigger tr_employeerecords_forinsert
on employeerecords
for insert
as
begin
declare @id int
select @id = id from inserted
insert into tbl_log
values
('employee added with id = '+cast(@id as nvarchar(10))+' at '+cast(getdate() as nvarchar(50)))
end

create trigger tr_testtrigger
on employeerecords
for update
as
begin
select * from deleted
select * from inserted
end

create trigger tr_employeerecords_forupdate
on employeerecords
for update
as
begin
declare @id int
declare @oldname nvarchar(100), @newname nvarchar(100)
declare @logdata nvarchar(1000)
select @id=id, @oldname =name from deleted
select @newname = name from inserted

set @logdata = 'employee updated with id = '+cast(@id as nvarchar(10))+' at '+cast(getdate() as nvarchar(50))+' name updated from '+ @oldname+ ' to '+@newname
insert into tbl_log
values
(@logdata)
end


create trigger tr_employeerecords_insteadofinsert
on employeerecords
instead of insert
as
begin
declare @email varchar(100)
SELECT @email = Email from inserted
insert into tbl_log values ('someone tried to insert inavalid email '+@email+ 'at '+cast(getdate() as nvarchar(50)))
end