47 lines
1.5 KiB
Transact-SQL
47 lines
1.5 KiB
Transact-SQL
-- 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
|
|
|