MSSQL DB - EX11

--10/12
-- Northwind 컨텍스트로 이동
USE Northwind

--1.커서 만들기
--p660
DECLARE customer_cursor CURSOR
FOR
SELECT
  companyname
FROM
 customers
 
OPEN customer_cursor
DECLARE @companyname nvarchar (40)
FETCH NEXT FROM customer_cursor INTO @companyname --@companyname 에 customer_cursor 대입
WHILE (@@FETCH_STATUS = 0) -- 데이타 확인( 데이타가 있다면 )
BEGIN
 PRINT @companyname
 FETCH NEXT FROM customer_cursor INTO @companyname
END

CLOSE customer_cursor
DEALLOCATE customer_cursor

--2. 커서 만들기2
DECLARE product_curssor CURSOR
FOR
SELECT
 productname, unitprice
FROM
 products

OPEN product_curssor

DECLARE @productname nvarchar(4)
DECLARE @unitprice money

FETCH NEXT FROM product_curssor INTO @productname, @unitprice
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT @productname + ' : ' +
   CONVERT(nvarchar (50), @unitprice)
 FETCH NEXT FROM product_curssor INTO @productname, @unitprice
END

CLOSE product_curssor
DECLARE  product_curssor

--exampledb 컨텍스트로 이동
USE exampledb

--
CREATE TABLE triggertest(no int identity, msg nvarchar (1000))
--DROP TRIGGER person_trigger
CREATE TRIGGER person_trigger
ON person
AFTER INSERT, DELETE, UPDATE
AS
 INSERT INTO triggertest VALUES ('데이터가 변경되었습니다.')

INSERT INTO person
VALUES
(
 3, '이나영', '1234', 'lny@empasle.com', 'business', getdate()
)
UPDATE person
SET phone = '0147'
WHERE number = 1

DELETE FROM person WHERE number = 2

SELECT * FROM triggertest

SELECT * FROM PEROSN 

-- 테스트
USE DOTNET6TH

SELECT * FROM dbo.contact