TRIGGERS SQL

---TRIGGERS:--

--They are like stored procedures but with some differences:-

---Stored procedures have to be explicitly called by the user,

--Triggers are automatically called when ever insert, delete, update or create, alter and drop statements are executed.

--Stored procedures do not need any explicit clause for their calling or creation.

--Triggers are used for providing security and for data cascading.

--Cascading:-

---If data changes are made in one table, then data changes should be automatically enforced in other tables.


----TYPES OF TRIGGERS IN SQL SERVER:--

---1)AFTER TRIGGERS:-DEFINED ON A TABLE ONLY.

---2)INSTEAD OF TRIGGERS:-DEFINED ON A TABLE OR VIEW.


---3)DDL TRIGGERS:-DEFINED ON A DATABASE.

--DDL TRIGGERS CAN BE USED TO PREVENT CREATE TABLE,ALTER TABLE,

--DROP TABLE STATEMENTS.



1)AFTER TRIGGERS:--DEFINED ON A TABLE ONLY:--
--select datepart("m",getdate())
create trigger trg on HotelEntry for insert,delete,update
as
if datepart("m",getdate())=6
begin
print 'sorry no transactions in june'
rollback transaction
end


2) INSTEAD OF TRIGGER
create trigger trp on HotelEntry instead of insert,delete,update
as
select * from hotel


---3) DDL TRIGGERS:--

create trigger dbdemotrig on database for create_table, alter_table, drop_table
as
print 'creating, alter, dropping of tables not allowed'
rollback transaction




----cascading triggers:--

----if insert ,update,delete are executed on a base table, then insert,delete,update should automatically happen on other tables.

---MAGIC TABLES:--

--Inserted and Deleted:---

----These are the clipboard tables which store a copy of the data as a result of -insert,delete or update operations:

---Inserted:Insert, Update queries
---Deleted:Delete, Update queries


select * from EmpMaster

create trigger trg on EmpMaster for insert,update
as
declare
@a int,
@b varchar(10)
---this command retreives the data from the clipboard table inserted.
select @a=inserted.empid, @b=inserted.empname from inserted
---pass the data in another table using 2nd query.
insert EmpMaster (empid,empname) values(@a,@b)
print 'inserted data'