SQL Queries for Cursors and Triggers

--forward omly global cursor by default
declare
for empcs cursor
select * from employ
open empcs
fetch next from empcs
close empcs
deallocate empcs
select @@FETCH_STATUS
update employ
set salary=60000
where CURRENT of empcs
fetch next from empcs
delete employwhere CURRENT of empcs
select * from employ
declare
for empcs cursor local
select * from employ
open empcs
fetch next from empcs
while(@@FETCH_STATUS=0)
begin
fetch next from empcs
end
deallocate empcs
declare
for empcs cursor static
select * from employ
open empcs
fetch next from empcs
fetch prior from empcs
fetch last from empcs
fetch first from empcs
fetch absolute 4 from empcs
fetch absolute -2 from empcs
fetch relative 2 from empcs
fetch relative -2 from empcs

--updations and deletions are not possible through static cursors

delete employ where current of empcs
update employ set salary=4444 where CURRENT of empcs
deallocate empcs

-- Dynamic Cursors
declare
for empcs cursor dynamic
select * from employ
open empcs
fetch next from empcs
fetch prior from empcs
fetch last from empcs
fetch first from empcs
fetch absolute 4 from empcs
fetch absolute -2 from empcs
fetch relative 2 from empcs
fetch relative -2 from empcs
delete employ where current of empcs
update employ
set salary=80000 where CURRENT of empcs

create table emp(eid int primary key,ename char(4))
insert into emp values(1,'aaa')
insert into emp values(2,'bbb')
insert into emp values(3,'ccc')
insert into emp values(4,'ddd')
insert into emp values(5,'eee')
insert into emp values(6,'fff')
select * from emp
declare
for empkcs cursor keyset
select * from emp
open empkcs
fetch absolute 3 from empkcs
fetch first from empkcs
fetch last from empkcs
fetch prior from empkcs
fetch relative 2 from empkcs
fetch next from empkcs
update emp
set ename='deep'where CURRENT of empkcs
delete emp where current of empkcs
select @@FETCH_STATUS
select * from employ
create trigger trgdelemploy on employ after delete as
select * from employ
begin
print 'deletion not allowed'
rollback transaction
select * from employ
end
delete employ
sp_helptrigger employ
sp_helptext trgdelemploy
drop table orders
drop table item

create table item(itemid char(4),itemstock int)
insert into item values('I01',300)
insert into item values('I02',400)
insert into item values('I03',500)
insert into item values('I04',600)
select * from item

create table orders(orderid char(4),itemid char(4),quantity int)
select * from item
select * from item
select * from orders
create trigger trginsord on orders after insert
as
begin
declare @istock int
declare @qtyordered int
declare @itemid char(4)
declare @qtydiff int
set @itemid=(select itemid from inserted)
set @istock=(select itemstock from item where itemid=@itemid)
set @qtyordered = (select quantity from inserted)
if (@qtyordered> @istock)
begin
print 'insufficient stocks'
rollback transaction
end
else
begin
update item
set itemstock=itemstock-@qtyordered
where itemid=@itemid
end
end
insert into orders values('O1','I01',30)
insert into orders values('O2','I02',40)
insert into orders values('O3','I03',500)
insert into orders values('O4','I04',700)
select * from item
select * from orders

create trigger trgdelord on orders after delete
as
begin
declare @qtycancelled int
declare @itemid char(4)
set @itemid=(select itemid from deleted)
set @qtycancelled = (select quantity from deleted)
update item
set itemstock=itemstock+@qtycancelled where itemid=@itemid
end

drop trigger trgdelord
delete from orders where orderid='O3'
drop view vwempdep
create view vwempdep as
select empid,fname,salary,e.deptid 'emp deptid',d.deptid,deptname,depthead from employ e join department d on e.deptid=d.deptid
select * from vwempdep

create trigger trgupdvwempdep on vwempdep instead of update as
begin
declare @salary int
declare @eid char(4)
declare @deptname varchar(20)
declare @deptid char(4)
set @salary=(select salary from inserted)
set @eid=(select empid from inserted)
set @deptname=(select deptname from inserted)
set @deptid=(select deptid from inserted)
update vwempdep
set salary=@salary where empid=@eid
update vwempdep
set deptname=@deptname where deptid=@deptid
end
update vwempdep
set salary=30000, deptname='HR' where empid='E06'

--DDL triggers
create trigger trgsafety on database drop_table,alter_table,drop_index
as
print 'you must disable or drop "trgsafety" trigger to make the structure change'
rollback

Cheers..!!
https://techiesss.blogspot.in/