Triggers

/*Trigger*/

Trigger is nothing but,its a process that will happen automatically if any database activities occurs..

Database Activties means... Any DDL  or DML or LogOn Activities on modifing data or schema (Not Selecting Records)

what is DDL What is DML

DDL - Data Definition Language -- So its about Table definition or schema....
CREATE,DROP,ALTER,TRUNCATE

DML - Data Manipulation Language -- So its about Table data modification...
INSERT,UPDATE,DELETE,SELECT

In this session, we can see about DML Triggers... (Trigger occurs when any data modification happened on table)


Ok. Now you all know the definition.. We can call trigger in two way, Before any data changes occur or after data changes occur...
--After Trigger
--Instead of Trigger

Now Examples....

/*First,After Trigger....*/

CREATE TABLE TEMP_RECORDS
(
SNO INT IDENTITY(1,1),
E_NAME VARCHAR(100)
)

INSERT INTO TEMP_RECORDS
SELECT 'Vicky'

Ok.. Lets create one trigger on this table....

We can create trigger for insert,update,delete seperately or compined

CREATE TRIGGER TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
FOR INSERT
AS
BEGIN
SELECT 'Hi... Records inserted successfully'
END

/*Insert Record now*/

INSERT INTO TEMP_RECORDS
SELECT 'Moov'

SELECT * FROM TEMP_RECORDS

/*
--So you can do any logic inside the trigger
--You can use, FOR or AFTER
*/

/*Lets try for the insert and update and delete*/

DROP TRIGGER TRIGGER_TEMP_RECORDS

CREATE TRIGGER TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
FOR INSERT,DELETE,UPDATE
AS
BEGIN

SELECT 'Hi... Trigger ''''TRIGGER_TEMP_RECORDS'''' fired'

IF UPDATE(E_NAME) /*Checking the update data modification-- E_NAME is the column name*/
SELECT 'Hi... Records Updated successfully'
END

INSERT INTO TEMP_RECORDS
SELECT 'anand'
/*Here Both statements are printed.. It means, Insert Statement modified the value of column E_NAME to new value*/

DELETE FROM TEMP_RECORDS where E_NAME = 'Moov'
/*Here One statement printed(Records Updated successfully).. It means, Delete Statement not update the value of column E_NAME*/

UPDATE TEMP_RECORDS
SET E_NAME = 'Vicky-TA'
WHERE E_NAME = 'Vicky'
/*Here Both statements are printed.. It means, update Statement modified the value of column E_NAME to new value*/

/*Now Insert New Column*/
ALTER TABLE TEMP_RECORDS
ADD AGE INT

/*Insert Records only in Age*/

INSERT INTO TEMP_RECORDS(AGE)
SELECT 10
select * from TEMP_RECORDS
/*Here Both statements are printed.. It means, Insert Statement modified the value of column E_NAME to null value*/

UPDATE TEMP_RECORDS
SET AGE = 10
WHERE E_NAME = 'Vicky'
/*Here One statement printed(Trigger ''TRIGGER_TEMP_RECORDS'' fired).. It means, update Statement on AGE column not considered in trigger.*/

/*This is the basics of Trigger... Here you learned we can do some logics while modifying data..*/

/*Next one Trigger with Magic Tables...*/

/*Magic tables are virtual tables, You can't select this table in real time, but u can select this in Run time of data modification.. Only in Trigger*/
/*
INSERTED,
DELETED
*/


ALTER TRIGGER TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
FOR INSERT,DELETE,UPDATE
AS
BEGIN

SELECT 'INSERTED',* FROM INSERTED
SELECT 'DELETED',* FROM DELETED
END


INSERT INTO TEMP_RECORDS
SELECT 'KC',20
/*
Here, No records deleted.. New values displayed in result set
*/

SELECT * FROM TEMP_RECORDS

DELETE FROM TEMP_RECORDS where E_NAME = 'anand'
/*
Here, No records Inserted.. Deleted values displayed in result set
*/

UPDATE TEMP_RECORDS
SET E_NAME = 'Vignesh'
WHERE E_NAME = 'Vicky-TA'

/*
Here, Inserted  and Deleted values displayed
*/

/*
So we can use Magic tables to identify old deleted values.. we can maintain it in another table
This is the logic we used in BAS Project
*/



/*Instead of Trigger*/
drop trigger TRIGGER_TEMP_RECORDS

drop trigger INSTEAD_TRIGGER_TEMP_RECORDS

CREATE TRIGGER INSTEAD_TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
INSTEAD OF INSERT,DELETE,UPDATE
AS
BEGIN

DECLARE @ename VARCHAR(100)
SELECT @ename = E_NAME FROM INSERTED /*Magic table used here*/

if @ename like '%vicky%'
begin
print 'You cant insert names like vicky'
rollback
end

END

/*Now try to insert record*/

truncate table TEMP_RECORDS

INSERT INTO TEMP_RECORDS
SELECT 'vicky',20

select * from TEMP_RECORDS
/*Errored out..Record not Inserted*/

INSERT INTO TEMP_RECORDS
SELECT 'kc',20
select * from TEMP_RECORDS
/*Record not inserted. You need to specify the insert statement inside trigger*/


ALTER TRIGGER INSTEAD_TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
INSTEAD OF INSERT,DELETE,UPDATE
AS
BEGIN

DECLARE @ename VARCHAR(100)
SELECT @ename = E_NAME FROM INSERTED /*Magic table used here*/

IF @ename like '%vicky%'
BEGIN
PRINT 'You cant insert names like vicky'
ROLLBACK
END
ELSE
begin
INSERT INTO TEMP_RECORDS
SELECT E_NAME,AGE from INSERTED

end

END

/*Now Insert same record*/


INSERT INTO TEMP_RECORDS
SELECT 'kc',20
SELECT * FROM TEMP_RECORDS

/*So, Instead Of Trigger used to restrict the INSERT/DELETE/UPDATE...*/


/*Ok.. We have seen only about DML Trigger.. DDL Trigger should be learned by self.. Because.. it can be used against database developers.. If anything wrong in DDL or long on trigger.. you cant use databse.*/


/*Output Clause*/


CREATE TABLE TEMP_RECORDS_output
(
SNO INT IDENTITY(1,1),
E_NAME VARCHAR(100)
)

insert into TEMP_RECORDS_output
OUTPUT inserted.*
select 'vicky'

delete from TEMP_RECORDS_output
OUTPUT deleted.*
where E_NAME = 'vicky'


insert into TEMP_RECORDS_output
OUTPUT inserted.* into temp_log
select 'vicky'

create table temp_log
(
new_value varchar(100),
old_value varchar(100)
)


update TEMP_RECORDS_output
set E_NAME = 'vicky_ta'
OUTPUT inserted.E_NAME,deleted.E_NAME into temp_log
where E_NAME = 'vicky'

select * from temp_log