MS-SQL 에서 Trigger, Function, Procedures 를 이용하여 System을 재구성 합니다.
최종 종착지는 Rest API 를 향하여 진행 하면서, SQL Anywhere 9(에 PK 만 있는 DB)를
MS-SQL 에 Trigger, Function, Procedures 로 모든 파워빌더 비즈니스 로직을 옮기는 과정에.... 잊어버릴까봐, 정리함.
테이블.트리거
USE [DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE ( or ALTER ) TRIGGER [dbo].[TriggerName]
ON [dbo].[TABLENAME]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
DECLARE @inColData as char(1)
DECLARE @deColData as char(1)
DECLARE @Action as char(1)
SET @Action = 'I'
IF EXISTS(SELECT * FROM DELETED)
BEGIN
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED) THEN 'U' ELSE 'D' END)
END
IF @Action = 'I'
BEGIN
SELECT @inColData = COL01 FROM INSERTED ;
END
IF @Action = 'D'
BEGIN
SELECT @deColData = COL01 FROM DELETED ;
END
IF @Action = 'U'
BEGIN
SELECT @inColData = COL01 FROM INSERTED ;
SELECT @deColData = COL01 FROM DELETED ;
END
END
GO
스칼라함수
USE [DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE ( or ALTER ) FUNCTION [dbo].[FunctionName] (
@Arg1 char(20), @Arg2 char(20), @Arg3 char(20)
)
RETURNS numeric(15,4)
AS
BEGIN
DECLARE @Reg_data numeric(15,4);
SET @Reg_data = 12.5 ;
return @Reg_data ;
END
GO