SQLtriggerassignment.sql

--- Trigger Assignment
create database trigger_1
use trigger_1
--- creating tblEmployee table:

create table  tblEmployee
(
  Id int Primary Key,
  name varchar(30),
  Salary int,
  Gender varchar(10),
  DepartmentId int
)
--- Inserting data in tblEmployee table

Insert into tblEmployee values 
(1,'Anjali',45000, 'female', 3),
(2,'suresh',45000, 'Male', 4),
(3,'Hetsi',45000, 'female', 5),
(4,'Het', 35400, 'Male', 2),
(5,'Abha', 36000, 'Female', 1);

Select * from tblEmployee

drop table tblemployee 

--- 1. Create an AFTER INSERT trigger to log new employees.

-- creating a log table 
create table employeelog 
(
id int, 
logdate datetime 
)

create trigger trg_lognewemployee
on tblemployee
after insert 
as
begin 
insert into employeelog(id, logdate)
select id, getdate()
from inserted ;
end

--- 2. Create a trigger to prevent salary < 10000.
create trigger trg_checksalary 
on tblemployee
for insert,update 
as 
begin
if exists( select * from inserted where salary < 10000)
begin 
print 'salary cannot be less than 10000'
rollback transaction 
end 
end 


--- 3. Create AFTER DELETE trigger to log deleted records.


--- 4. How to disable a trigger?


--- 5. How to enable a trigger?


--- 6. How to drop a trigger?


--- 7. Prevent deletion of Manager role.


--- 8. Create trigger to prevent duplicate email.


--- 9. How to check existing triggers on a table?


--- 10.Create a trigger to prevent UPDATE of primary key.


--- 11.Create a trigger to prevent table drop.


--- 12.Create a trigger to log when an employee record is deleted.


--- 13.Create a trigger to prevent negative stock quantity.


--- 14.Create a trigger to ensure the Salary is non negative.


--- 15.Disable all triggers on a table.