use master
create database bindu
on primary
(
name='vinod',
filemane='C:vinodvinodbindu.mdf',
size=10mb,
maxsize=20mb
)
log on
(
name = 'bindu',
filename = 'C:vinodindu_log.ldf',
size = 5mb,
maxsize = 10mb
)

--Day5 13/08/2010 DB-Development
_________________________________________________________
TRIGGERS

1.) DDL Triggers:- (Database triggers) Fires on DDL statements like CREATE TABLE,CREATE VIEW,DROP TABLE, etc
2.) DML Triggers:-(Table triggers) Fires on DML statements like INSERT,DELETE and UPDATE......

Use of triggers:-
Data security like rolling back un-wanted operations
Automatic Calculations and Operations

syntax:
CREATE TRIGGER droptrig
on database
for drop_table as print 'You cannot DROP tables in this database, Remove Trigger first'
rollback

drop table employees
--Error: You cannot DROP tables in this database, Remove Trigger first
--Msg 3609, Level 16, State 2, Line 1
--The transaction ended in the trigger. The batch has been aborted.

create trigger updtrig
on employees
for UPDATE,delete
as
print 'You cant update from this table, Remove the trigger'
rollback

drop trigger updtrig

select * from employees
update employees set salary = 10034 where ecode = 101
--You cant update from this table, Remove the trigger
--Msg 3609, Level 16, State 1, Line 1
--The transaction ended in the trigger. The batch has been aborted.

Two special virtual tables exist inside trigger block are:-
-Deleted
-Inserted
*Their structure will be as per the table on which trigger is fired
*Deleted record are available inside "deleted" table
*New inserted records are available inside "Inserted" table
*In case of update,old record is deleted and kept inot deleted table and a new record is inserted which is kept in inserted table
*these special tables are visible only inside the trigger block

eg. Log the deleted record in your log table

create table log_table
(
ecode int,
ename varchar(20),
deptid int,
salary money,
time_of_oper datetime
)

select * from log_table

create trigger deltrig
on employees
for delete
as
declare @ec int,@en varchar (20),@did int, @sal money, @time datetime
select @ec = ecode,@en = ename,@did = deptid,@sal = salary,@time = getdate()
from deleted
Insert into Log_table values (@ec,@en,@did,@sal,@time)
print 'Record deleted and logged into log_table'

select * from employees
delete employees where ecode = 102

select * from Log_table
select * from dbo.join_view
select * from employees

select * from dapartment
--A view cant be updated if its from multiple tables and the update is on multiple table columns
create trigger join_view_trig
on join_view
instead of update
as
declare  @did int,@old_did int
select @new_did =dept_did from inserted
select @old_did = dept_did from deleted
update dapartment set deptid = @did where deptid = @old_did
update employees set deptid = @did where deptid = @old_did

Test: update join_view set emp_did = 206,dept_did = 206 where dept_did = 202

Assignment:
Q: Create two tables procuct and order with the following columns and put some records in the product table:
Product: Prodid,productname,qty,price
Order: orderid,orderqty,prodid

Define a trigger such that whenever the order is placed for any product, it should be checked in the product table
whether the ordered quantity is available in the product table. If not, the ordered transaction should be rejected
else it should be accepted in the order table and quantity should be updated in the product table as well

create table product
(
prodid int,
productname varchar(50),
qty int,
price money
)
select * from product

create table orderT
(
orderid int,
Orderqty int,
prodid int
)
drop table ordert

select * from ordert
select * from product
insert into product values (1,'Nokia X6',5,16000)
insert into product values (2,'Blackberry',2,13000)
insert into product values (3,'Sony Ericsson Xperia',1,18000)
insert into product values (4,'Samsung corby',4,8000)
insert into product values (5,'Micromax Q7',8,8000)
insert into product values (6,'LG Cookie',4,5000)

alter trigger assignment
on ordert
for insert
as
declare @Qty int,@prdid int
declare @Qty1 int
select @Qty = Orderqty,@prdid=prodid from inserted
select @Qty1 = qty from product where prodid = @prdid
if (@Qty<@Qty1)
 update product set qty= (@Qty1-@Qty) where prodid = @prdid
else begin
 rollback
 print 'Stock lesser than the quantity mentioned. Available Quantity is:'+convert(char(5),@Qty1)
 print 'Your Transaction has been rejected'
end
select * from ordert
select * from product
insert into ordert values (2,4,1)

alter table test_auto
(
rollno int IDENTITY ,
name varchar(10)
)

insert into test_auto(name) values('vinod')
insert into test_auto(name) values('Bindu')
insert into test_auto(name) values('vinu')
insert into test_auto(name) values(Rani')

SELECT * FROM test_auto
CREATE TABLE [dbo].[test_auto](
 [rollno] [int] IDENTITY(1,10) NOT NULL,
 [name] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

*Reccursive Triggers:- A trigger fires another trigger.
A very dangerous situation it is... May end up in loops of Triggers...
Direct Reccursion: When a trigger fires again the same trigger
Indirect Reccursion: When a triggergets fired again by another child trigger
 
This process is called nesting of trigger. 32 levels of nesting of triggers are allowed....
By default this is on. But can be disabled if needed
Statement to Enable and Disable

sp_configure 'nested triggers',0
_________________________________________________________
Dynamic SQL

use AdventureWorks

select * from sys.tables
select * from HumanResources.Employee

declare @SQL_Str nvarchar(max)
declare @mgrid varchar(20)
select @mgrid = '16,6,12'
select @SQL_Str = 'select * from HumanResources.Employee where managerID in ('+@mgrid+')'
select @SQL_Str
exec sp_executesql @SQL_STR

exec(@SQL_Str)

exec sp_dyn_sql '16,6'
===========================================================================================
create procedure sp_dyn_sql(@mgrid varchar(20))
as
declare @SQL_Str nvarchar(max)
select @SQL_Str = 'select * from HumanResources.Employee where managerID in ('+@mgrid+')'
select @SQL_Str
exec sp_executesql @SQL_STR
or
exec(@SQL_Str)
===================================================================
drop procedure sp_dyn_sql
select * from HumanResources.Employee

_________________________________________________________
INDEXES

Clustered: Sorts the data pages
Non-Clustered: Data pages aren sorted instead, index pages are created which actually store the information of data pages
Fill Factor: Related to data pages to prevent page splitting during indexing. This Indexing is faster. A table can have only one.
Pad Index: Related to Index Pages. This Indexing is slower. A Table can hav 249.

select * from HumanResources.Employee
use employeeDB

syntax:
create index idx_emp
on employees(ecode)
--Default: nonclustered index

create clustered index idx_emp_clust --also CREATE NONCLUSTERED INDEX can be used
on employees(ename)

alter index idx_emp_clust on employees Disable
Note: Indexes can only be created,dropped,enabled or disabled
drop index idx_emp_clust on employees

--On primary key column automatically clustered index is created
create index idx
on employees (ecode)
with (fillfactor = 5,
pad_index = on)

Note: Fillfactor and pad index parameters are used to optimize page splitting in case of frequent updations
Fragmentation: When spaces are created because of deletion of records
Defragmentation: Is used to reorganise the indexes

Rebuilding of indexes: It drops old indexes and creates new ones             --for a lot of fragmentation
Reorganising of indexes: It just rearranges the existing index without deleting it   --for fragmentation < 30%

How to we know %age of fragmentation of Indexes:
All Indexes details of Indexes are maintatined at:
select * from sys.sysindexes

Readymade Query for showing fragmentation:

select a.index_id,name,avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID(N'EmployeeDB'),
OBJECT_ID(N'dbo.employees'),Null,Null,Null) As a Join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id

-- > 30%
Alter index idx on employees rebuild

-- < 30%
Alter index idx on employees reorganize
select * from employees
select * from employees where ename like 'a%'

_________________________________________________________
Full text search

select businessentityid,jobtitle from humanresources.employee where freetext(*,'Marketting Assistant')
select businessentityid,jobtitle from humanresources.employee where contains(Jobtitle,'Marketting OR Assistant')
select businessentityid,jobtitle from humanresources.employee where contains(Jobtitle,'Marketting AND Assistant')

create table letters
(
id int,
letters nvarchar(max)
)
use employeeDB
select letters from test_ftx where contains(letters,'system and prog')
select letters from test_ftx where freetext(*,'system and Gemini')

insert into test_ftx values (1,'An operating system (OS) is a set of system software (programs and data) running on a computer that manage the computer hardware and provide common services for execution of various application software.

For hardware functions such as input and output a')

select * from test_ftx

Q: use AdventureWorks
select * from Humanresources.JobCandidate
select * from Humanresources.JobCandidateHistory

create Trigger dJobCandidate
on Humanresources.JobCandidate
for delete
as
declare @CondID int,@Resum xml,@RejDate datetime,@CntID int
select @CondID = JobCandidateID, @Resum = Resume, @RejDate = getdate(), @CntID = null from deleted
insert into Humanresources.JobCandidateHistory(JobCandidateID,Resume,RejectedDate,ContactID) values (@CondID,@Resum,@RejDate,@CntID)

delete Humanresources.JobCandidate where JobCandidateID=2

select * from Humanresources.JobCandidateHistory