use FishingMania
select * from MsCustomer
--CURSOR & TRIGGER
declare view_customer cursor scroll
--for select * from MsCustomer
FOR select
from MsCustomer
open view_customer
--FETCH NEXT FROM view_customer
--FETCH PRIOR FROM view_customer
FETCH RELATIVE 2 FROM view_customer
--tembak datanya
FETCH ABSOLUTE 15 from view_customer
--variabel global yg kasi tau fetch status sblmny, karna ga pilih data
FETCH NEXT FROM view_customer
while @@FETCH_STATUS = 0
FETCH NEXT FROM view_customer
--next maju, prior mundur
DECLARE view_customer CURSOR SCROLL
FOR SELECT * FROM MsCustomer
----------
OPEN view_customer
FETCH NEXT FROM view_customer
FETCH PRIOR FROM view_customer
FETCH ABSOLUTE 15 FROM view_customer
FETCH FIRST FROM view_customer
FETCH LAST FROM view_customer
---------
FETCH LAST FROM view_customer
WHILE (@@FETCH_STATUS = 0)
FETCH PRIOR FROM view_customer
SELECT @@FETCH_STATUS
------
declare @name varchar(100)
declare @email varchar(100)
FETCH NEXT FROM view_customer into @name, @email
WHILE @@FETCH_STATUS = 0
--==0 ada data, -1 gaada
begin
print(@name + ' ' + @email)
FETCH NEXT FROM view_customer into @name, @email
end
----
FETCH NEXT FROM view_customer
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM view_customer
------
CLOSE view_customer
DEALLOCATE view_customer
-----
declare view_transaction cursor scroll
for select [Link], [Link] from MsCustomer mc
join TransactionHeader th on [Link] = [Link]
open view_transaction
declare @customerName varchar(255)
declare @transactionId char (5)
declare @transactionDate Date
fetch first from view_transaction into @customername, @transactionID,
@transactionDate
while @@FETCH_STATUS =0
begin
print('invoice number: '+ @transactionID)
print('Date : '+convert(varchar, @transactionDate))
print('Customer Name : '+@customerName)
declare @fishId char(5)
declare @fishName varchar(100)
declare @fishPrice float
declare @quantity int
declare @no int
declare @totalPrice float
set @no = 1
set @totalPrice = 0
declare view_detail cursor for
select [Link], [Link], [Link], [Link]
from TransactionDetail td
join MsFish mf on [Link] = [Link]
where [Link] = @transactionId
open view_detail
fetch next from view_detail into @fishId, @fishName, @fishPrice, @quantity
while @@FETCH_STATUS=0
begin
print(Cast(@no as varchar) + '. '+@fishName + ' - ' + cast(@fishPrice as
varchar) + ' - ' + cast(@quantity as varchar) + ' pcs')
set @totalPrice = @totalPrice + @fishPrice * @quantity
set @no = @no +1
fetch next from view_detail into @fishId, @fishName, @fishPrice, @quantity
end
print('Total price ' + cast(@totalPrice as varchar))
--fetch next from view_transaction into @customername, @transactionID,
@transactionDate
--end
CLOSE view_transaction
DEALLOCATE view_transaction
tanpa scroll cuma next
next pun bisa pake scroll juga
===============
go
declare view_transaction cursor scroll
for select [Link], [Link], [Link]
from MsCustomer mc
join TransactionHeader th
on [Link]=[Link]
open view_transaction
declare @customerName varchar(255)
declare @transactionID char(5)
declare @transactionDate date
fetch first from view_transaction into
@customerName,@transactionID,@transactionDate
while @@FETCH_STATUS=0
begin
print(@customerName+' '+@transactionID+' '+cast(@transactionDate as varchar))
declare @fishID char(5)
declare @fishName varchar(100)
declare @fishPrice float
declare @quantity int
declare @no int
declare @totalPrice float
set @no=1
set @totalPrice=0
declare view_detail cursor scroll for
select [Link], [Link], [Link], [Link] from TransactionDetail td
join MsFish mf on [Link]=[Link]
where [Link]=@transactionID
open view_detail
fetch next from view_detail into @fishID,@fishName,@fishPrice,@quantity
while @@FETCH_STATUS=0
begin
print(cast(@no as varchar)+'. '+@fishname +' '+cast(@fishPrice as varchar)+'
'+cast(@quantity as varchar))
set @totalPrice=@totalPrice+@fishPrice*@quantity
set @no= @no +1
fetch next from view_detail into @fishID,@fishName,@fishPrice,@quantity
end
print('Total Price '+cast(@totalPrice as varchar))
close view_detail
deallocate view_detail
fetch next from view_transaction into
@customerName,@transactionID,@transactionDate
End
close view_transaction
deallocate view_transaction
go
--TRIGER----------------------
--INSERT UPDATE DELETE
SELECT * FROM MsCustomer
INSERT INTO MsCustomer VALUES
('CU017', 'Udin', 'Male', 'Greek Street', 'bejo@[Link]', '2025-12-1'),
('CU018', 'CHika', 'FeMale', 'Apple Street', 'bejo@[Link]', '2025-12-1')
begin tran
rollback
go
--instead of,ga lakuin yg dikanannya (insert) tapi yg didalam trigger dilakuin
alter trigger new_customer on MsCustomer
instead of insert
as
begin
declare @dob date
select CustomerDOB = CustomerDOB from inserted
select * from inserted
--inserte & deleted
--if DATEDIFF(Day, @dob, GETDATE()) < 0
--begin
-- print('Invalid Customer DOB')
-- rollback Transaction
--end
--else
-- print('Success')
end
select DATEDIFF(day, Getdate(), '2022-07-26')
--for kalo
--DELETE-----------
--trigger ga hanya 1 event
select * from Mscustomer
go
delete MsCustomer where CustomerName = 'bejo'
update MsCustomer set CustomerName = 'Udin'
where CustomerName = 'Type'
go
create or alter trigger delete_customer on MsCustomer
instead of update, delete
as
begin
if exists (ehere select * from deleted)
begin
end
select * from inserted
select * from deleted
diam - 0
if(exists(SELECT * FROM INFORMATION_ [Link])
begin select * into deletedCustomer from deleted
end
else
begin
DECLARE @customerID CHAR(5)
DECLARE @customerName VARCHAR(100)
DECLARE @customerGender VARCHAR(20)
DECLARE @customerAddress VARCHAR(255)
DECLARE @customerEmail VARCHAR(100)
DECLARE @customerD
OB DATE
--SELECT
--@customerID = CustomerID,
--@customerName = CustomerName,
--@customerGender = CustomerGender,
--@customerAddress = CustomerAddress,
--@customerEmail = CustomerEmail,
--@customerDOB = CustomerDOB
--FROM deleted
--insert into @customerID = CustomerID,
--@customerName = CustomerName,
--@customerGender = CustomerGender,
--@customerAddress = CustomerAddress,
--@customerEmail = CustomerEmail,
--@customerDOB = CustomerDOB
_
end
