SQL Server 2000 View SP Trigger Microsoft CREATE

SQL Server 2000 세미나 View, SP &Trigger 주최: Microsoft 강사: 정원혁

만들기/ 고치기/ 지우기/ 암호화 CREATE [owner. ]VIEW_name (column_name]. . . )] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION] VIEW [,

with check option v 시스템 뷰 SELECT * FROM sysobjects WHERE type = 'V' SELECT * FROM information_schema. views

색인 (Indexed view) CREATE TABLE tempdb. . a ( c 1 int , c 2 int , c 3 as (c 1 * c 2) ) insert tempdb. . a values (2, 3) go create index idx on tempdb. . a (c 3)

색인된 뷰 use northwind go CREATE VIEW v. Total. Product WITH SCHEMABINDING AS SELECT productid, sum( unit. Price * Quantity) as Total , sum(quantity) as tot. Qty , COUNT_BIG(*) as count FROM dbo. [order details] GROUP BY productid Go CREATE UNIQUE CLUSTERED INDEX idx ON v. Total. Product (productid)

파티션드 뷰 CREATE TABLE SUPPLY 1 ( supply. ID INT PRIMARY KEY CHECK (supply. ID BETWEEN 1 and 150), supplier CHAR(50) ) …. . CREATE TABLE SUPPLY 4 ( supply. ID INT PRIMARY KEY CHECK (supply. ID BETWEEN 451 and 600), supplier CHAR(50) )

파티션드 뷰 CREATE VIEW all_supplier_view AS SELECT * FROM SUPPLY 1 UNION ALL SELECT * FROM SUPPLY 2 UNION ALL SELECT * FROM SUPPLY 3 UNION ALL SELECT * FROM SUPPLY 4

만들기/ 고치기 / 지우기 CREATE PROC[EDURE] procedure_name [; number] [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [, . . . n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS sql_statement [. . . n]

만들기 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS sql_statement [. . . n ] } ….

수행 순서 v sp_settriggerorder 'My. Trigger', 'first', 'UPDATE' n n FIRST LAST NONE 하나씩만 지정가능

INSTEAD OF Trigger Example v Non-updateable view CREATE VIEW Authors. View AS SELECT au_fname+ ' ' + au_lname as au_name, au_id, phone, address, city, state, zip, contract FROM authors

INSTEAD OF Trigger Example CREATE TRIGGER Au. Ins on Authors. View INSTEAD OF INSERT AS BEGIN INSERT authors(au_id, au_fname, au_lname, …) SELECT au_id, dbo. Extract. Name. Part(au_name, 1), dbo. Extract. Name. Part(au_name, 2), phone, address, city, state, zip, contract FROM inserted END INSERT Authors. View VALUES(‘John Doe’, …)

다중 행 처리 (1) alter trigger trg 2 on sales 2 for insert as declare @r int , @i int set @i = 0 select @r = count(*) from inserted while @i < @r begin insert sales 3 values ('aa', 10) set @i = @i + 1 end

다중 행 처리 (2) alter trigger trg 2 on sales 2 for insert as insert sales 3 select * from inserted go