134 lines
5.1 KiB
Transact-SQL
134 lines
5.1 KiB
Transact-SQL
USE <database, sysname, DB>
|
|
GO
|
|
|
|
-- drop both CodeDes and Code tables
|
|
IF OBJECT_ID('<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des', 'U') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID('<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>', 'U') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>
|
|
END
|
|
GO
|
|
|
|
-- ** CODE TABLE ** --
|
|
-- create Code table
|
|
CREATE TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>
|
|
(
|
|
<table_suffix, sysname, CoTbl>_PK int IDENTITY(1,1) NOT NULL,
|
|
Code nvarchar(x) NOT NULL,
|
|
Sort int NOT NULL,
|
|
IsActive bit NOT NULL,
|
|
|
|
Db_Status nvarchar(1) NOT NULL,
|
|
Db_InsDt smalldatetime NOT NULL,
|
|
Db_InsUser nvarchar(50) NOT NULL,
|
|
Db_UpdDt smalldatetime NULL,
|
|
Db_UpdUser nvarchar(50) NULL,
|
|
Db_Timestamp timestamp NOT NULL,
|
|
|
|
CONSTRAINT PK_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> PRIMARY KEY CLUSTERED(<table_suffix, sysname, CoTbl>_PK ASC)
|
|
)
|
|
GO
|
|
|
|
-- define update trigger
|
|
CREATE TRIGGER ut_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> ON <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> AFTER UPDATE
|
|
AS
|
|
BEGIN
|
|
UPDATE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>
|
|
SET Db_UpdDt = getdate(),
|
|
Db_UpdUser = right(suser_sname(), 50),
|
|
Db_Status = CASE WHEN isnull(i.Db_Status, '') = 'D' THEN 'D' ELSE 'U' END
|
|
FROM inserted i
|
|
WHERE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>.<table_suffix, sysname, CoTbl>_PK = i.<table_suffix, sysname, CoTbl>_PK
|
|
END
|
|
GO
|
|
|
|
-- set unique index on Code
|
|
CREATE UNIQUE NONCLUSTERED INDEX IX_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>__Unique_Code ON <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> (Code ASC)
|
|
GO
|
|
|
|
-- set default-value constraints
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> ADD
|
|
CONSTRAINT DF_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>_IsActive
|
|
DEFAULT ((1)) FOR IsActive
|
|
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> ADD
|
|
CONSTRAINT DF_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>_DbStatus
|
|
DEFAULT ('I') FOR Db_Status
|
|
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> ADD
|
|
CONSTRAINT DF_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>_DbInsDt
|
|
DEFAULT (getdate()) FOR Db_InsDt
|
|
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> ADD
|
|
CONSTRAINT DF_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>_DbInsUser
|
|
DEFAULT (right(suser_sname(),(50))) FOR Db_InsUser
|
|
GO
|
|
-- ** CODE TABLE ** --
|
|
|
|
|
|
|
|
-- ** DESCRIPTION TABLE ** --
|
|
-- create CodeDes table
|
|
CREATE TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des
|
|
(
|
|
<table_suffix, sysname, CoTbl>Des_PK int IDENTITY(1,1) NOT NULL,
|
|
<table_suffix, sysname, CoTbl>_FK int NOT NULL,
|
|
Lang nvarchar(2) NOT NULL,
|
|
Descr nvarchar(max) NULL,
|
|
|
|
Db_Status nvarchar(1) NOT NULL,
|
|
Db_InsDt smalldatetime NOT NULL,
|
|
Db_InsUser nvarchar(50) NOT NULL,
|
|
Db_UpdDt smalldatetime NULL,
|
|
Db_UpdUser nvarchar(50) NULL,
|
|
Db_Timestamp timestamp NOT NULL,
|
|
|
|
CONSTRAINT PK_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des PRIMARY KEY CLUSTERED(<table_suffix, sysname, CoTbl>Des_PK ASC)
|
|
)
|
|
GO
|
|
|
|
-- define update trigger
|
|
CREATE TRIGGER ut_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des ON <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des AFTER UPDATE
|
|
AS
|
|
BEGIN
|
|
UPDATE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des
|
|
SET Db_UpdDt = getdate(),
|
|
Db_UpdUser = right(suser_sname(), 50),
|
|
Db_Status = CASE WHEN isnull(i.Db_Status, '') = 'D' THEN 'D' ELSE 'U' END
|
|
FROM inserted i
|
|
WHERE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des.<table_suffix, sysname, CoTbl>Des_PK = i.<table_suffix, sysname, CoTbl>Des_PK
|
|
END
|
|
GO
|
|
|
|
-- set default-value constraints
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des ADD
|
|
CONSTRAINT DF_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des_DbStatus
|
|
DEFAULT ('I') FOR Db_Status
|
|
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des ADD
|
|
CONSTRAINT DF_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des_DbInsDt
|
|
DEFAULT (getdate()) FOR Db_InsDt
|
|
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des ADD
|
|
CONSTRAINT DF_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des_DbInsUser
|
|
DEFAULT (right(suser_sname(),(50))) FOR Db_InsUser
|
|
GO
|
|
|
|
-- set foreign-key constraints
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des WITH CHECK ADD
|
|
CONSTRAINT FK_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des__<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>
|
|
FOREIGN KEY(<table_suffix, sysname, CoTbl>_FK)
|
|
REFERENCES <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl> (<table_suffix, sysname, CoTbl>_PK)
|
|
|
|
ALTER TABLE <table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des
|
|
CHECK CONSTRAINT FK_<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>Des__<table_prefix, sysname, TBL>_<table_suffix, sysname, CoTbl>
|
|
GO
|
|
|
|
-- ** DESCRIPTION TABLE ** --
|
|
|