ZPTRIGGER

Ó Zpaulo Carraca, 2004

Write business rules using conditions (T-SQL), message texts (plain text) and actions (T-SQL) and store them in a database table.

These rules will be automatically called by a general purpose trigger generated dynamically by the Software.

Business rules that are written in a database table are seen both by developers and application users.

Qualified users will be able to create rules of various levels of complexity, according to software skills and business responsibility level. This can be achieved without changing the software or manipulating many and hard to understand parameters. These rules are independent of software application versions and software upgrades. Rule maintenance is easy, as the rule is softly linked to the table that triggers it.

How it works

After installing the Software in the database, which is achieved by running the script zptrigger.sql on query analyzer or on a similar environment, all tables and software modules are ready to be used and the product can be fully used.

The main table, called zpc_trg, records the business rules that are to be respected. This table can be accessed using SQL Server Enterprise Manager, Query Analyzer or any interface or program that can connect to the Database.

Trg_tab: The name of the table to be used for rule creation. Mandatory, must refer to a user table of the Database.

Trg_level: The level of the rule. Mandatory, integer. The system administrator can reserve the initial range for himself (ex 0 to 9) and set other intervals for different kinds of users. This ordering prevents unauthorized users to perform actions (create, update, delete) in levels that are not accessible to them. The order of execution of rules is given by the ascending order of trg_level (0, 1, 2, ...).

Trg_seq: Sequential number for rules, within the same table and level. The ascending sequence is respected in the execution order of rules. Mandatory, integer.

Trg_ins, trg_upd e trg_del: Three flags (0=no; 1=yes) to set the action that triggers the test of the rule (insert, update or delete). Any combination is valid. All flags are mandatory, tiny integer.

Trg_errtype: Type of error. Mandatory, tiny integer. Allowed values: 0, 1 or 2, meaning that, should the condition be true, the user will not get any message (0), will be warned but can proceed with the operation (1), or get a message and be prevented to carry on the current step (2).

Trg_rollb: Explicit rollback on trigger, if fatal error. Mandatory, tiny integer. Allowed values: 0 no rollback; 1 rollback.

The application that uses a Database with the Software may be written in a way that favors the use of trg_rollb as 0. In this case the Software does not ensure the rule is respected.

Trg_cond: The text, in T-SQL that states the condition to be tested. An initial “IF” is automatically assumed, and should not be written.

Trg_msg_cod: A code that points to a message that is to be displayed when the condition is true. The message text resides in view zpc_msg. This view is to be customized by the system administrator to point to existing tables with application messages. If this option is not to be used, codes and messages can be written in table zpc_msg1.

Trg_msg: Message text to be displayed when the condition is true and there is no value in trg_msg_cod or that value does not exist in view zpc_msg.

Trg_action: The action that takes place when the condition is true, written in T-SQL syntax. When the action to be performed is complex it is advisable to create a Database procedure and call it in trg_action, followed by the necessary parameters.

Trg_help_pk: This is an informational column not to be modified by the user. The Software will automatically update the value after the insert of each line. Then, this column shows, in SQL syntax, the ‘where’ condition for the table, should there be a primary key constraint. Example, in database ‘Pubs’, table ‘titleauthor’ has primary key ‘au_id’ and ‘title_id’, trg_help_pk would show ‘au_id = @au_id and title_id = @title_id’. This text can be used in the ‘where’ clause in trg_action to update the current line.

T-SQL texts have the need to refer to the contents of columns of the line in use. In insert and delete operations these variables have the name of the column prefixed with “@”. In update, old values are prefixed with “@p_” whereas new values are prefixed with “@”.

Examples

Technical Description

DB Object Catalog

zpc_log procedure creates a table for logging and a line in zpc_trg (level 99, sequence 1) to write into the log table. This line must be customized by the administrator to set the conditions in which it is to be used, eventually, the line can be duplicated to comprehend differences between insert, update and delete. Level 99 should be used, for easier readability.

zpc_ptdel procedure deletes the dynamic software associated with the tables in zpc_tdel.

zpc_trg_mdyn trigger dynamically creates a trigger for the table named in trg_tab, if it does not exists. This trigger is named zpd_x_<table_name>, where x can be “i”,”u” or “d”, standing for insert, update or delete. A procedure, named zpd_sqltest_<table_name> is also created, if it does not exists.

Additional Functions

a) Procedure to create a job (zpc_job), getting as input the name of the job, the step to run and the time delay to execution, in minutes.

b) table zpc_tlog and procedures zpc_ptlog and zpc_log to automatically create a log of data manipulation (insert, update or delete). The way to use this log can be customized, as the rule is written in table zpc_trg.

c) Table zpc_tdel and procedure zpc_ptdel to delete software automatically generated by the Software. This can be needed when a new column is added or a column is removed or has a new definition and they are used by the rules. After removing the software associated with the modified table, or tables, any update of a line in zpc_trg for that table or tables will cause a new generation of automatic software, taking in account the new definitions.

d) The use of warning messages (trg_errtype = 1) needs the information of the user to bypass the error. To accomplish this, a new column, with a fixed name and definition, must be added to the table. Use “alter table <table_name> add zpcokmsg tinyint null“. When this column is greater than 0, the warning error will not show up. If it is equal to 1, the trigger automatically will put it equal to 0, and future operations will need to change this value if warning messages are to be avoided. A value greater than 1 will inhibit warning error messages for that line, as long as zpcokmsg remains greater than 1.

Functionalities b) and c) are ready to be run after lines are inserted. The trigger cannot perform table related operations but creates a job to be run later on. SQL Server 7, in an ODBC environment, tries to run the job immediately, giving an error. Query Analyzer, if a job with the same name already exists in SQL Server Agent, will work properly. Given this inconvenience, the triggers for zpc_log and zpc_del are temporarily deactivated and the jobs must be run out of data manipulation. In Query Analyzer, run ‘exec ptlog’ or ‘exec ptdel’, to create a log or to delete dynamically generated software, respectively.

Known Limitations

Most tables and definitions are handled by the Software. In case working fields will overflow, a message will be issued. In spite of all the efforts to avoid unexpected error situations, these will be trapped by the Database Management System, which ensures that the work will be properly rolled back. The user or the system administrator must change the status of that rule and report the error.

Column names which use reserved names are not supported, a version using names between [] can be developed.

Contacts

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_job_del]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[zpc_job_del]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_job_log]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[zpc_job_log]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_trg_mdyn]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[zpc_trg_mdyn]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_fields]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[zpc_fields]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_job]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[zpc_job]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_log]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[zpc_log]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_ptdel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[zpc_ptdel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_ptlog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[zpc_ptlog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_colcon]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[zpc_colcon]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_coltot]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[zpc_coltot]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_msg]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[zpc_msg]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_err]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zpc_err]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_msg1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zpc_msg1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_tdel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zpc_tdel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_tlog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zpc_tlog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zpc_trg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zpc_trg]
GO

CREATE TABLE [dbo].[zpc_err] (
[err] [bit] NULL ,
[spid] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[zpc_msg1] (
[msg_cod] [varchar] (12) NOT NULL ,
[msg_text] [varchar] (200) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[zpc_tdel] (
[table_name] [sysname] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[zpc_tlog] (
[table_name] [sysname] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[zpc_trg] (
[trg_tab] [varchar] (50) NOT NULL ,
[trg_level] [int] NOT NULL ,
[trg_seq] [int] NOT NULL ,
[trg_status] [tinyint] NOT NULL ,
[trg_ins] [tinyint] NOT NULL ,
[trg_upd] [tinyint] NOT NULL ,
[trg_del] [tinyint] NOT NULL ,
[trg_errtype] [tinyint] NOT NULL ,
[trg_rollb] [tinyint] NOT NULL ,
[trg_cond] [varchar] (1000) NOT NULL ,
[trg_msg_cod] [varchar] (12) NULL ,
[trg_msg] [varchar] (200) NULL ,
[trg_action] [varchar] (1000) NULL ,
[trg_help_pk] [nvarchar] (500) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zpc_msg1] WITH NOCHECK ADD
CONSTRAINT [pk_zpc_msg1] PRIMARY KEY NONCLUSTERED
(
[msg_cod]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zpc_tdel] WITH NOCHECK ADD
CONSTRAINT [pk_zpc_tdel] PRIMARY KEY NONCLUSTERED
(
[table_name]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zpc_tlog] WITH NOCHECK ADD
CONSTRAINT [pk_zpc_tlog] PRIMARY KEY NONCLUSTERED
(
[table_name]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zpc_trg] WITH NOCHECK ADD
CONSTRAINT [pk_zpc_trg] PRIMARY KEY NONCLUSTERED
(
[trg_tab],
[trg_level],
[trg_seq]
) ON [PRIMARY] ,
CHECK ([trg_del] = 0 or [trg_del] = 1),
CHECK ([trg_errtype] = 0 or [trg_errtype] = 1 or [trg_errtype] = 2),
CHECK ([trg_ins] = 0 or [trg_ins] = 1),
CHECK ([trg_rollb] = 0 or [trg_rollb] = 1),
CHECK ([trg_upd] = 0 or [trg_upd] = 1)
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE VIEW zpc_colcon
AS
-- drop view zpc_colcon
SELECT concol.table_name, concol.column_name, conname.constraint_name, conname.constraint_type
FROM [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] concol
LEFT OUTER JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] conname
ON concol.constraint_name = conname.constraint_name
AND conname.table_name = concol.table_name
WHERE conname.constraint_type = 'PRIMARY KEY'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE VIEW zpc_coltot
AS
-- drop view zpc_coltot
SELECT col.table_name, col.column_name, col.ordinal_position, col.data_type,
isnull(col.character_maximum_length,0) as maxlen , col.character_octet_length,
col.numeric_precision, col.numeric_scale, col.datetime_precision, col.is_nullable
,colcon.constraint_name, colcon.constraint_type
FROM sysobjects obj
join
[INFORMATION_SCHEMA].[COLUMNS] col
on obj.name = col.table_name
LEFT OUTER JOIN zpc_colcon colcon
ON col.column_name = colcon.column_name
AND col.table_name = colcon.table_name
WHERE obj.xtype = 'U'
AND obj.name <> 'dtproperties'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE VIEW zpc_msg
/* view to be modified using table with messages from each environment
if application software does not have such a table, use zpc_msg1
*/
AS
SELECT msg_text msg_text , msg_cod msg_cod FROM zpc_msg1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE zpc_fields
-- drop procedure zpc_fields
-- given a table name suplies strings with fields
@in_table_name sysname ,
-- -- strings with variables for:
@@fs_sel nvarchar(1000) OUTPUT, -- select
@@fs_ftx nvarchar(1000) OUTPUT, -- fetch
@@fs_dcl nvarchar(1000) OUTPUT, -- data def
@@fs_dcn nvarchar(1000) OUTPUT, -- data def with not null clause for create table
@@fs_dyn nvarchar(1000) OUTPUT, -- param def for dynamic sql
@@fs_dy0 nvarchar(1000) OUTPUT, -- param def for dynamic sql = null as a constant
@@fs_idv nvarchar(0200) OUTPUT, -- PK col names and values
@@fs_idd nvarchar(0200) OUTPUT, -- PK col names
@@fs_iid nvarchar(0400) OUTPUT, -- PK col names(PK) for inserted n and deleted o
@@fs_con tinyint OUTPUT, -- 0 no id, 1 - PK, (2 - UNIQUE IX not used any more)
@@fs_okf tinyint OUTPUT, -- 0 no ok field, 1 - yes
@@fs_errlen nvarchar(0300) OUTPUT -- Message with field with length problem
AS
DECLARE
@ln_var int ,
@ln_id int ,
@ln_iid int ,
@w_col sysname ,
@w_pos sysname ,
@w_type sysname ,
@w_len int ,
@w_local int ,
@w_len1 int ,
@w_prec int ,
@w_scal int ,
@w_precd int ,
@w_null nvarchar(3) ,
@w_nnull nvarchar(10) ,
@w_con sysname ,
@w_cont sysname ,
@w_tab sysname ,
@w_first bit ,
@w_count int ,
@w_pk_u_con sysname ,
@w_col_type nvarchar(200) ,
@w_pk_u_str nvarchar(200) ,
@w_pk_u sysname
BEGIN
-- init length of fields MUST be = definition
set @ln_var = 1000
set @ln_id = 200
set @ln_iid = 400
-- init flag for ok field
SELECT @@fs_okf = 0
-- search for ID of table (primary key or unique constraint)
SELECT @w_pk_u_con = NULL -- no unique id (pk or u ix)
SELECT @@fs_con = 0 -- no unique id (pk or u ix)
SELECT @w_pk_u_con = constraint_name FROM zpc_coltot
WHERE table_name = @in_table_name
and constraint_type = 'PRIMARY KEY'
IF @w_pk_u_con IS NOT NULL
BEGIN
SELECT @@fs_con = 1
END
/*
IF @w_pk_u_con IS NULL
BEGIN
SELECT @w_pk_u_con = constraint_name FROM zpc_coltot
WHERE table_name = @in_table_name
and constraint_type = 'UNIQUE'
IF @w_pk_u_con IS NOT NULL
BEGIN
SELECT @@fs_con = 2
END
END
ELSE
BEGIN
SELECT @@fs_con = 1
END
*/
-- get all info from fields on given table
DECLARE cur_col CURSOR FOR
SELECT column_name, ordinal_position, data_type,
maxlen, isnull(character_octet_length, 0),
isnull(numeric_precision, 0) , isnull(numeric_scale,0), isnull(datetime_precision , 0),
isnull(constraint_name, 'no') , isnull(constraint_type, 'no'), upper(isnull(is_nullable, 'NO'))
FROM zpc_coltot
WHERE table_name = @in_table_name
SELECT @w_first = 1
OPEN cur_col
FETCH NEXT FROM cur_col INTO @w_col,@w_pos,@w_type,@w_len,@w_len1,@w_prec,@w_scal,
@w_precd,@w_con,@w_cont, @w_null
WHILE @@fetch_status = 0
BEGIN
-- flag columns that cannot be used as local variables
IF @w_type in ('text', 'ntext', 'image')
BEGIN
SELECT @w_local = 0
END
ELSE
BEGIN
SELECT @w_local = 1
END
-- build strings
-- variables are prefixed with @
SELECT @w_col_type = '@' + RTRIM(@w_col) + ' ' + RTRIM(@w_type)
-- build datatypes definition
IF @w_type in ('char', 'varchar', 'nchar', 'nvarchar')
BEGIN
SELECT @w_col_type = RTRIM(@w_col_type) + '(' + LTRIM(str(@w_len)) + ')'
END
IF @w_type in ('int', 'tinyint')
BEGIN
SELECT @w_col_type = RTRIM(@w_col_type)
SELECT @w_len = @w_prec
END
IF @w_type in ('decimal', 'numeric')
BEGIN
SELECT @w_col_type = RTRIM(@w_col_type) + '(' + LTRIM(str(@w_prec)) + ',' + LTRIM(str(@w_scal)) + ')'
SELECT @w_len = @w_prec
END
IF @w_type in ('float', 'real')
BEGIN
SELECT @w_col_type = RTRIM(@w_col_type) + '(' + LTRIM(str(@w_prec)) + ')'
SELECT @w_len = @w_prec
END
IF @w_type in ('binary', 'varbinary')
BEGIN
SELECT @w_col_type = RTRIM(@w_col_type) + '(' + LTRIM(str(@w_len)) + ')'
END
-- If uid
IF @w_con = @w_pk_u_con
BEGIN
SELECT @w_pk_u_str = '" ' + RTRIM(@w_col) + ' = " + ' +
'CAST(@' + RTRIM(@w_col) + ' as varchar(' + LTRIM(STR(@w_len)) + ') ) '
SELECT @w_pk_u = RTRIM(@w_col)
END
ELSE
BEGIN
SELECT @w_pk_u_str = null
SELECT @w_pk_u = null
END
-- null clause
IF @w_null = 'NO'
BEGIN
SELECT @w_nnull = ' NOT NULL'
END
ELSE
BEGIN
SELECT @w_nnull = ' '
END
-- if ok field
IF @w_col = 'zpcokmsg'
BEGIN
SELECT @@fs_okf = 1
END
-- build output strings
IF @w_first = 1 -- first field
BEGIN
SELECT @@fs_dcn = RTRIM(@w_col_type) + RTRIM(@w_nnull)
IF @w_local = 1
BEGIN
SELECT @@fs_sel = RTRIM(@w_col)
SELECT @@fs_ftx = '@' + RTRIM(@w_col)
SELECT @@fs_dcl = RTRIM(@w_col_type)
SELECT @@fs_dyn = '@' + RTRIM(@w_col) + '=@' + RTRIM(@w_col)
SELECT @@fs_dy0 = '@' + RTRIM(@w_col) + '=null'
SELECT @@fs_idv = RTRIM(@w_pk_u_str )
SELECT @@fs_idd = RTRIM(@w_pk_u )
SELECT @@fs_iid = 'n.' + RTRIM(@w_pk_u ) + ' = o.' + RTRIM(@w_pk_u )
SELECT @w_first = 0
END
END
ELSE -- not first field
BEGIN
SELECT @@fs_dcn = RTRIM(@@fs_dcn) + ',' + RTRIM(@w_col_type) + RTRIM(@w_nnull)
IF @w_local = 1
BEGIN
SELECT @@fs_sel = RTRIM(@@fs_sel) + ',' + RTRIM(@w_col)
SELECT @@fs_ftx = RTRIM(@@fs_ftx) + ',@' + RTRIM(@w_col)
SELECT @@fs_dcl = RTRIM(@@fs_dcl) + ',' + RTRIM(@w_col_type)
SELECT @@fs_dyn = RTRIM(@@fs_dyn) + ',@' + RTRIM(@w_col) + '=@' + RTRIM(@w_col)
SELECT @@fs_dy0 = RTRIM(@@fs_dy0) + ',@' + RTRIM(@w_col) + '=null'
IF @w_pk_u_str is not null
BEGIN
SELECT @@fs_idv = RTRIM(@@fs_idv) + '+' + RTRIM(@w_pk_u_str)
SELECT @@fs_idd = RTRIM(@@fs_idd) + ', ' + RTRIM(@w_pk_u)
SELECT @@fs_iid = RTRIM(@@fs_iid) + ' and n.' + RTRIM(@w_pk_u ) + ' = o.' + RTRIM(@w_pk_u )
END
END
END
/*
print "read col " + RTRIM(@w_col)
+ " pos " + LTRIM(str(@w_pos))
+ " type " + RTRIM(@w_type)
+ " len " + LTRIM(str(@w_len))
+ " len1 " + LTRIM(str(@w_len1))
+ " prec " + LTRIM(str(@w_prec))
+ " scal " + LTRIM(str(@w_scal))
+ " precd " + LTRIM(str(@w_precd))
+ " con " + RTRIM(@w_con)
+ " cont " + RTRIM(@w_cont)
+ " fff " + RTRIM(@@fs_sel)
*/
FETCH NEXT FROM cur_col INTO @w_col,@w_pos,@w_type,@w_len,@w_len1,@w_prec,@w_scal,
@w_precd,@w_con,@w_cont, @w_null
END
CLOSE cur_col
DEALLOCATE cur_col
-- test length
SELECT @@fs_errlen = ':'
IF LEN(@@fs_sel) = @ln_var
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' sel'
IF LEN(@@fs_ftx) = @ln_var
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' ftx'
IF LEN(@@fs_dcl) = @ln_var
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' dcl'
IF LEN(@@fs_dcn) = @ln_var
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' fdcn'
IF LEN(@@fs_dyn) = @ln_var
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' dyn'
IF LEN(@@fs_dy0) = @ln_var
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' dy0'
IF LEN(@@fs_idv) = @ln_id
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' idv'
IF LEN(@@fs_idd) = @ln_id
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' idd'
IF LEN(@@fs_iid) = @ln_iid
SELECT @@fs_errlen = RTRIM(@@fs_errlen) + ' iid'
IF @@fs_errlen <> ':'
SELECT @@fs_errlen = 'table ' + RTRIM(@in_table_name) + ' with length problems in variables'
+ RTRIM(@@fs_errlen)
END -- end zpc_fields
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE zpc_job
-- DROP PROCEDURE zpc_job
-- Creates a Job given the following data @in_job_name sysname , -- job name
@in_what nvarchar(500) , -- what to do
@in_min_plus int -- delay in minutes from present time
AS
DECLARE
@JobID BINARY(16) ,
@ReturnCode INT ,
@lname sysname ,
@w_msg varchar(100) ,
@w_dbname sysname ,
@w_owner_login_name sysname ,
@d1 datetime ,
@d2 datetime ,
@d1_y varchar(04) ,
@d1_m varchar(02) ,
@d1_d varchar(02) ,
@d2_h varchar(02) ,
@d2_m varchar(02) ,
@d2_s varchar(02) ,
@d_date varchar(08) ,
@d_time varchar(06) ,
@w_fillpos char(2)

-- init variables
SELECT @ReturnCode = 0
SELECT @w_dbname = db_name()
SELECT @w_owner_login_name = suser_sname()
SELECT @d1 = getdate()
SELECT @d2 = dateadd(minute, @in_min_plus , @d1 ) -- run proc in given delay, in minutes
SELECT @d1_y = datepart(year , @d1)
SELECT @d1_m = datepart(month , @d1)
IF len(@d1_m) < 2 SELECT @d1_m = '0' + @d1_m
SELECT @d1_d = datepart(day , @d1)
IF len(@d1_d) < 2 SELECT @d1_d = '0' + @d1_d
SELECT @d2_h = datepart(hour , @d2)
IF len(@d2_h) < 2 SELECT @d2_h = '0' + @d2_h
SELECT @d2_m = datepart(minute , @d2)
IF len(@d2_m) < 2 SELECT @d2_m = '0' + @d2_m
SELECT @d2_s = datepart(second , @d2)
IF len(@d2_s) < 2 SELECT @d2_s = '0' + @d2_s
SELECT @d_date = @d1_y + @d1_m + @d1_d
SELECT @d_time = @d2_h + @d2_m + @d2_s
/*
print ' d1 ' + cast(@d1 as varchar(20))
print ' d1 y ' + str(@d1_y )
print ' d1 m ' + str(@d1_m )
print ' d1 d ' + str(@d1_d )
print ' date ' + @d_date
print ' d2 h ' + str(@d2_h )
print ' d2 m ' + str(@d2_m )
print ' d2 s ' + str(@d2_s )
print ' time ' + @d_time
return
*/
BEGIN TRANSACTION
-- cleanup
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if itt exists)
SELECT @JobID = job_id FROM msdb.dbo.sysjobs
WHERE (name = @in_job_name)
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
SELECT @w_msg = 'Unable to import job ' + @in_job_name +
' since there is already a multi-server job with this name.'
RAISERROR (@w_msg, 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = @in_job_name
SELECT @JobID = NULL
END
BEGIN
--
-- Do it
--
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = @in_job_name ,
@owner_login_name = @w_owner_login_name,
@description = N'No description ', @category_name = N'[Uncategorized (Local)]', @enabled = 1,
@notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0,
@notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
-- Add the job step
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1,
@step_name = N'zpd_autom',
@command = @in_what, @database_name = @w_dbname,
@server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0,
@flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'',
@on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
-- upd job
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @in_job_name, @enabled = 1, @freq_type = 1,
@active_start_date = @d_date , @active_start_time = @d_time
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
RAISERROR ('Error in zpd automatic job creation . ' , 16, 1)
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE zpc_log
-- drop PROCEDURE zpc_log
/*
given a table name creates a new table for logging
and inserts a general purpose, customizable line for logging action, in zpc_trg
*/
@in_table_name sysname
-- WITH ENCRYPTION
AS
DECLARE
@@fs_sel nvarchar(1000) ,
@@fs_ftx nvarchar(1000) ,
@@fs_dcl nvarchar(1000) ,
@@fs_dcn nvarchar(1000) ,
@@fs_dyn nvarchar(1000) ,
@@fs_dy0 nvarchar(1000) ,
@@fs_idv nvarchar(0200) ,
@@fs_idd nvarchar(0200) ,
@@fs_iid nvarchar(0400) ,
@@fs_con tinyint ,
@@fs_okf tinyint ,
@@fs_errlen nvarchar(0300) ,
@w_a_exec nvarchar(3000) ,
@w_con nvarchar(0020) ,
@w_cont nvarchar(0350) ,
@w_next int ,
@w_sqli nvarchar(2000) ,
@w_tabname sysname ,
@w_exist int

BEGIN
select @w_tabname = ltrim(@in_table_name) + 'zplog'
SELECT @w_exist = id FROM sysobjects
WHERE name = @w_tabname
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
-- if log does not already exists, do it
IF @w_exist IS NULL
BEGIN
-- get info of fields on choosen table
EXECUTE zpc_fields @in_table_name , @@fs_sel OUTPUT,
@@fs_ftx OUTPUT ,@@fs_dcl OUTPUT, @@fs_dcn OUTPUT, @@fs_dyn OUTPUT, @@fs_dy0 OUTPUT,
@@fs_idv OUTPUT, @@fs_idd OUTPUT, @@fs_iid OUTPUT, @@fs_con OUTPUT, @@fs_okf OUTPUT,
@@fs_errlen OUTPUT
-- test return msg
IF @@fs_errlen is not null
BEGIN
RAISERROR (@@fs_errlen , 15 , 1)
RETURN
END
-- if table has no unique id, log is useless
IF @@fs_con <> 0
BEGIN
-- get type of constraint
IF @@fs_con = 1
BEGIN
select @w_con = 'PRIMARY KEY'
END
/* IF @@fs_con = 2
BEGIN
select @w_con = 'UNIQUE'
END
*/
SELECT @w_cont = ' ADD CONSTRAINT '
+ rtrim(@in_table_name) + '_idlog ' + @w_con + '(' + @@fs_idd + ', zpdtstamp ' + ')'
-- get rid of @ in string of columns
SELECT @@fs_dcl = REPLACE (@@fs_dcl , '@' , ' ')
-- create and alter table dynamically
select @w_a_exec = "CREATE TABLE " + @w_tabname
+ "( " + replace (@@fs_dcn, '@' , '') + " , zpdtstamp datetime NOT NULL)"
-- PRINT @w_a_exec
EXEC (@w_a_exec)
select @w_a_exec = "ALTER TABLE " + @w_tabname
+ @w_cont
-- PRINT @w_a_exec
EXEC (@w_a_exec)
-- create line in zpc_trg with generic rule: insert log in every DML operation, to be updated by user
/* to avoid duplications get a specific key for log, level 99 , seq 1
select @w_next = max(trg_seq) from zpc_trg
where trg_tab = @in_table_name
and trg_level = 10
if @w_next is null
begin
select @w_next = 0
end
select @w_next = @w_next + 1
*/
select @w_sqli = "insert into " + ltrim(@w_tabname) + " ("
+ @@fs_sel + ", " + "zpdtstamp ) values ("
+ @@fs_ftx + ", getdate() )"
insert into zpc_trg (
trg_tab, trg_level, trg_seq, trg_ins, trg_upd, trg_del,
trg_cond, trg_msg_cod, trg_msg, trg_action , trg_errtype, trg_status ) values
( @in_table_name, 99, 1, 1 , 1 , 1 ,
'1 = 1' , null , null , @w_sqli , 0 , 0 )
END
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE zpc_ptdel
-- DROP PROCEDURE zpc_ptdel
-- deletes dynamic objects (proc and triggeers) for tables named in zpc_tdel
--
-- bypass trigger incapacity to perform commmit-based operations
-- trigger creates job, that job runs this proc (can also be run stand-alone)
AS
DECLARE
@w_tab sysname ,
@w_obj sysname ,
@w_sql nvarchar(300)

BEGIN
DECLARE cur_del CURSOR FOR SELECT table_name FROM zpc_tdel
OPEN cur_del
FETCH NEXT from cur_del INTO @w_tab
WHILE @@fetch_status = 0
BEGIN
-- drop proc
select @w_obj = 'zpd_sqltest_' + @w_tab
if exists (select * from sysobjects where id = object_id(@w_obj) and OBJECTPROPERTY(id, N'IsProcedure') = 1)
exec ('drop procedure ' + @w_obj)
-- drop trigger insert
select @w_obj = 'zpd_i_' + @w_tab
if exists (select * from sysobjects where id = object_id(@w_obj) and OBJECTPROPERTY(id, N'IsTrigger') = 1)
exec ('drop trigger ' + @w_obj)
-- drop trigger update
select @w_obj = 'zpd_u_' + @w_tab
if exists (select * from sysobjects where id = object_id(@w_obj) and OBJECTPROPERTY(id, N'IsTrigger') = 1)
exec ('drop trigger ' + @w_obj)
-- drop trigger delete
select @w_obj = 'zpd_d_' + @w_tab
if exists (select * from sysobjects where id = object_id(@w_obj) and OBJECTPROPERTY(id, N'IsTrigger') = 1)
exec ('drop trigger ' + @w_obj)
-- delete row
DELETE FROM zpc_tdel where table_name = @w_tab
FETCH NEXT from cur_del INTO @w_tab
END
CLOSE cur_del
DEALLOCATE cur_del
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE zpc_ptlog
/*
proc to be executed periodically
trigger inserts into zpc_tlog because trigger cannot execute CREATE TABLE
then, this proc, for every table name in table a_tlog, runs zpc_log and deletes line
*/
AS
DECLARE
@w_nome_tab sysname

BEGIN
DECLARE cur_log CURSOR FOR SELECT table_name FROM zpc_tlog
OPEN cur_log
FETCH NEXT from cur_log INTO @w_nome_tab
WHILE @@fetch_status = 0
BEGIN
EXECUTE zpc_log @w_nome_tab
DELETE FROM zpc_tlog where table_name = @w_nome_tab
FETCH NEXT from cur_log INTO @w_nome_tab
END
CLOSE cur_log
DEALLOCATE cur_log
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [zpc_job_del] ON [zpc_tdel]
FOR INSERT
AS
IF @@version like '%7.0%'
RETURN
ELSE
EXECUTE zpc_job 'zpctrgjob' , 'exec zpc_ptdel' , 2

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [zpc_job_log] ON [zpc_tlog]
FOR INSERT
AS
IF @@version like '%7.0%'
RETURN
ELSE
EXECUTE zpc_job 'zpctrgjob' , 'exec zpc_ptlog' , 2

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [zpc_trg_mdyn] ON [zpc_trg]
FOR INSERT, UPDATE
AS
-- DROP TRIGGER zpc_trg_mdyn
-- for every line inserted/updated:
-- a) test validity of sql statements [creaates a proc, to do it, if it does not exits
-- (NB. if sql fails proc creationn is also rolled back) ]
-- b) if table has no automatic trigger forr ins,upd,del, generates it
--
DECLARE
@tab nvarchar(0050) ,
@seq int ,
@w_tab sysname ,
@w_procname sysname ,
@w_text varchar(1024) ,
@w_action varchar(1024) ,
@msg varchar(0050) ,
@w_err tinyint ,
@w_id nvarchar(0200) ,
@w_cur_dyn sysname ,
@w_cur_dynu sysname ,
@w_cur_trg sysname ,
@w_exist int ,
@w_do_i int ,
@w_do_u int ,
@w_do_d int ,
@w_do_any int ,
@w_exe00 nvarchar(0020) ,
@w_exe00i nvarchar(0010) ,
@w_exe00u nvarchar(0010) ,
@w_exe00d nvarchar(0010) ,
@w_exe01 nvarchar(0300) ,
@w_exe01i nvarchar(0010) ,
@w_exe01u nvarchar(0010) ,
@w_exe01d nvarchar(0010) ,
@w_exe03 nvarchar(1500) ,
@w_exe05 nvarchar(1500) ,
@w_exe05a nvarchar(0020) ,
@w_exe08id nvarchar(0200) ,
@w_exe08i nvarchar(0030) ,
@w_exe08d nvarchar(0030) ,
-- @w_exe08u1 nvarchar(3000) , -- @w_exe08u2 nvarchar(3000) , -- @w_exe08u3 nvarchar(3000) , -- @w_exe08u4 nvarchar(3000) , @w_exe08u5 nvarchar(1500) ,
@w_exe08u6 nvarchar(1500) ,
@w_exe08u7 nvarchar(3500) ,
@w_exe_open nvarchar(0200) ,
@w_exe_cur nvarchar(1500) ,
@w_exe_curu nvarchar(1500) ,
@w_exe_cur_stat nvarchar(0050) ,
@w_exe_cur_trg nvarchar(0300) ,
@w_exe20 nvarchar(1000) ,
@w_exe20a nvarchar(0040) ,
@w_exe25 nvarchar(2000) ,
@w_exe28 nvarchar(0200) ,
@w_exe30 nvarchar(0500) ,
@w_exe45 nvarchar(0250) ,
@w_exe50 nvarchar(1500) ,
@w_exe90 nvarchar(0500) ,
@w_exe90u nvarchar(0500) ,
@w_exe95 nvarchar(0300) ,
@w_exe99 nvarchar(0300) ,
@trg_i tinyint ,
@trg_u tinyint ,
@trg_d tinyint ,
@help_pk nvarchar(0400) ,
@trg_type varchar (0001) ,
@@fs_sel nvarchar(1000) ,
@@fs_ftx nvarchar(1000) ,
@@fs_dcl nvarchar(1000) ,
@@fs_dcn nvarchar(1000) ,
@@fs_dyn nvarchar(1000) ,
@@fs_dy0 nvarchar(1000) ,
@@fs_idv nvarchar(0200) ,
@@fs_idd nvarchar(0200) ,
@@fs_iid nvarchar(0400) ,
@@fs_con tinyint ,
@@fs_okf tinyint ,
@@fs_errlen nvarchar(0300) ,
@@w_ret int ,
@fs_seln nvarchar(1100) ,
@ln_seln int ,
@fs_selo nvarchar(1100) ,
@ln_selo int ,
@fs_dclo nvarchar(1200) ,
@ln_dclo int ,
@fs_dclt nvarchar(2200) ,
@fs_ftxo nvarchar(1200) ,
@ln_ftxo int ,
@fs_dyno nvarchar(1200) ,
@ln_dyno int ,
@fs_dy0o nvarchar(1200) ,
@ln_dy0o int ,
@line_break nvarchar(2)

BEGIN
print ' »»» V 83'
-- set length of variables for control MUST be = to declare def. as above
set @ln_dclo = 1200
set @ln_seln = 1100
set @ln_selo = 1100
set @ln_ftxo = 1200
set @ln_dyno = 1200
set @ln_dy0o = 1200
-- initialize variables
select @w_err = 0
SELECT @line_break = CHAR(13) + CHAR(10)
-- test recursiveness
IF ISNULL (trigger_nestlevel( object_ID('zpc_trg_mdyn') ) , 0) > 1
BEGIN
RETURN -- NO Recursiveness
END
-- def. cursor for lines inserted/updated
DECLARE cur_ins CURSOR FOR SELECT trg_tab, trg_seq, trg_ins, trg_upd, trg_del,
trg_cond, trg_action
FROM inserted
-- work with every line
OPEN cur_ins
FETCH NEXT from cur_ins INTO @tab, @seq, @trg_i, @trg_u, @trg_d, @w_text, @w_action
WHILE @@fetch_status = 0
BEGIN
-- table name must be an existing user table
SELECT @w_tab = name FROM sysobjects WHERE name = @tab AND xtype = "U"
IF @w_tab IS NULL
BEGIN
SELECT @msg = "Table " + RTRIM(@tab) + " does not exists/not a user table"
RAISERROR (@msg , 15 , 1)
SELECT @w_err = 1
BREAK
END
-- get info of fields on choosen table
EXECUTE zpc_fields @w_tab , @@fs_sel OUTPUT, @@fs_ftx OUTPUT, @@fs_dcl OUTPUT, @@fs_dcn OUTPUT,
@@fs_dyn OUTPUT, @@fs_dy0 OUTPUT, @@fs_idv OUTPUT, @@fs_idd OUTPUT, @@fs_iid OUTPUT,
@@fs_con OUTPUT, @@fs_okf OUTPUT, @@fs_errlen OUTPUT
-- test return msg for length in zpc_fields
IF @@fs_errlen > ':'
BEGIN
RAISERROR (@@fs_errlen , 15 , 1)
SELECT @w_err = 1
BREAK
END
-- param for columns of table deleted @p_...
SELECT @fs_dclo = REPLACE (@@fs_dcl, '@' , '@p_')
SELECT @fs_dclt = RTRIM (@@fs_dcl) + ',' + RTRIM(@fs_dclo)
SELECT @fs_dy0o = REPLACE (@@fs_dy0, '@' , '@p_')
-- Test length
IF LEN(@fs_dclo) = @ln_dclo
BEGIN
RAISERROR ('Overflow in dclo. Contact support ' , 15 , 1)
SELECT @w_err = 1
BREAK
END
IF LEN(@fs_dy0o) = @ln_dy0o
BEGIN
RAISERROR ('Overflow in dy0o. Contact support ' , 15 , 1)
SELECT @w_err = 1
BREAK
END
-- test proc existance
SELECT @w_procname = "zpd_sqltest_" + RTRIM(@w_tab)
SELECT @w_exist = id FROM sysobjects
WHERE name = @w_procname
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
-- if proc does not exist : create it
IF @w_exist IS NULL
BEGIN -- Build the proc to test the SQL statement
/* PRINT "CREATE PROCEDURE " + @w_procname + @line_break
+ "@w_sql nvarchar(3000), " + @line_break
+ "@w_parm nvarchar(3000) , " + @line_break
+ "@@w_result int OUTPUT " + @line_break
+ "AS " + @line_break
+ "DECLARE " + @line_break
+ @@fs_dcl + ',' + @fs_dclo + @line_break
+ "BEGIN " + @line_break
+ " EXECUTE sp_executesql @w_sql, @w_parm," + @line_break
+ " " + @@fs_dy0 + "," + @fs_dy0o + @line_break
+ " IF @@ERROR <> 0 " + @line_break
+ " BEGIN" + @line_break
+ " SELECT @@w_result = @@error " + @line_break
+ " END " + @line_break
+ "END"
*/
-- create the dynamicaly generated proc
EXEC ( "CREATE PROCEDURE " + @w_procname + @line_break
+ "@w_sql nvarchar(3000), " + @line_break
+ "@w_parm nvarchar(3000) , " + @line_break
+ "@@w_result int OUTPUT " + @line_break
+ "AS " + @line_break
+ "DECLARE " + @line_break
+ @@fs_dcl + ',' + @fs_dclo + @line_break
+ "BEGIN " + @line_break
+ " EXECUTE sp_executesql @w_sql, @w_parm," + @line_break
+ " " + @@fs_dy0 + "," + @fs_dy0o + @line_break
+ " IF @@ERROR <> 0 " + @line_break
+ " BEGIN" + @line_break
+ " SELECT @@w_result = @@error " + @line_break
+ " END " + @line_break
+ "END" )
IF @@ERROR <> 0
BEGIN
RAISERROR ('Error in CREATION of PROCEDURE' , 15 , 1)
SELECT @w_err = 1
BREAK
END
END
-- print 'proc ok'
-- check both sql statements
-- NB. if a future version enables cheking syntax without executing, we will use it
IF @w_text IS NOT NULL
BEGIN
-- get the sql condition in good syntax
SELECT @w_text = "IF (" + @w_text + ") BEGIN RAISERROR('OK', 1, 1) END"
-- execute the proc for sql condition
EXECUTE @w_procname @w_text , @fs_dclt , @@w_ret
IF @@w_ret <> 0
BEGIN
RAISERROR ('Invalid SQL in condition ' , 15 , 1)
SELECT @w_err = 1
BREAK
END
END
-- execute the proc for sql action
IF @w_action IS NOT NULL
BEGIN
-- get the sql action with no actual action
SELECT @w_action = "IF 1 = 2 BEGIN " + RTRIM(@w_text) + " END "
-- test without getting a rollback
-- SELECT @w_action = REPLACE (UPPER(@w_action) , 'ROLLBACK' , 'return' )
EXECUTE @w_procname @w_action , @fs_dclt , @@w_ret
IF @@w_ret <> 0
BEGIN
RAISERROR ('Invalid SQL in action ' , 15 , 1)
SELECT @w_err = 1
BREAK
END
END
-- print 'sql test ok'
-- test automatic_trigger need and existance
SELECT @w_do_i = 0
SELECT @w_do_u = 0
SELECT @w_do_d = 0 -- insert
IF ISNULL(@trg_i , 0) = 1
BEGIN
SELECT @w_exist = NULL -- bug turnaround: select giving null does not update var
SELECT @w_id = "zpd_i_" + rtrim(@w_tab)
SELECT @w_exist = id FROM sysobjects WHERE name = @w_id
AND OBJECTPROPERTY(id, N'IsTrigger') = 1
IF @w_exist IS NULL
BEGIN
SELECT @w_do_i = 1
END
END
-- update
IF ISNULL(@trg_u , 0) = 1
BEGIN
SELECT @w_exist = NULL
SELECT @w_id = "zpd_u_" + rtrim(@w_tab)
SELECT @w_exist = id FROM sysobjects WHERE name = @w_id
AND OBJECTPROPERTY(id, N'IsTrigger') = 1
IF @w_exist IS NULL
BEGIN
SELECT @w_do_u = 1
END
END
-- delete
IF ISNULL(@trg_d , 0) = 1
BEGIN
SELECT @w_exist = NULL
SELECT @w_id = "zpd_d_" + rtrim(@w_tab)
SELECT @w_exist = id FROM sysobjects WHERE name = @w_id
AND OBJECTPROPERTY(id, N'IsTrigger') = 1
IF @w_exist IS NULL
BEGIN
SELECT @w_do_d = 1
END
END
-- general tasks if any trigger is to be created
SELECT @w_do_any = @w_do_i + @w_do_u + @w_do_d
IF @w_do_any > 0
BEGIN
-- generate sel statements for inserted n(ew) and deleted o(ld)
SELECT @fs_seln = REPLACE(@@fs_ftx , '@' , 'n.')
SELECT @fs_selo = REPLACE(@@fs_ftx , '@' , 'o.')
SELECT @fs_ftxo = REPLACE(@@fs_ftx , '@' , '@p_')
SELECT @fs_dyno = REPLACE(@@fs_dyn , '@' , '@p_')
-- Test length
IF LEN(@fs_ftxo) = @ln_ftxo
BEGIN
RAISERROR ('Overflow in ftxo. Contact support ' , 15 , 1)
SELECT @w_err = 1
BREAK
END
IF LEN(@fs_seln) = @ln_seln
BEGIN
RAISERROR ('Overflow in seln. Contact support ' , 15 , 1)
SELECT @w_err = 1
BREAK
END
IF LEN(@fs_selo) = @ln_selo
BEGIN
RAISERROR ('Overflow in selo. Contact support ' , 15 , 1)
SELECT @w_err = 1
BREAK
END
IF LEN(@fs_dyno) = @ln_dyno
BEGIN
RAISERROR ('Overflow in dyno. Contact support ' , 15 , 1)
SELECT @w_err = 1
BREAK
END
-- generate cursor names
SELECT @w_cur_dyn = "cur_dyn_" + RTRIM(@w_tab)
SELECT @w_cur_dynu = "cur_dynu_" + RTRIM(@w_tab)
SELECT @w_cur_trg = "cur_trg_" + RTRIM(@w_tab)
-- key strings may be null
SELECT @@fs_idv = ISNULL(@@fs_idv , '"NoKey"')
--
-- strings to build triggers text
--
SELECT @w_exe00 = "CREATE TRIGGER ["
SELECT @w_exe00i = "zpd_i_"
SELECT @w_exe00u = "zpd_u_"
SELECT @w_exe00d = "zpd_d_"
SELECT @w_exe01 = RTRIM(@w_tab) + "] ON [" + RTRIM(@w_tab) + "] "
+ " FOR "
SELECT @w_exe01i = " INSERT "
SELECT @w_exe01u = " UPDATE "
SELECT @w_exe01d = " DELETE "
SELECT @w_exe03 = CHAR(13) + CHAR(10)
+ "AS " + CHAR(13) + CHAR(10)
+ "DECLARE " + CHAR(13) + CHAR(10)
+ "@w_recur int ," + CHAR(13) + CHAR(10)
+ "@w_cur_i sysname ," + CHAR(13) + CHAR(10)
+ "@w_cur_u sysname ," + CHAR(13) + CHAR(10)
+ "@w_cur_d sysname ," + CHAR(13) + CHAR(10)
+ "@w_sqlcon nvarchar(1000) ," + CHAR(13) + CHAR(10)
+ "@w_sqlact nvarchar(1000) ," + CHAR(13) + CHAR(10)
+ "@w_msg nvarchar(0200) ," + CHAR(13) + CHAR(10)
+ "@w_msgm nvarchar(0200) ," + CHAR(13) + CHAR(10)
+ "@w_msgo nvarchar(0200) ," + CHAR(13) + CHAR(10)
+ "@w_pk int ," + CHAR(13) + CHAR(10)
+ "@w_nr_i int ," + CHAR(13) + CHAR(10)
+ "@w_nr_d int ," + CHAR(13) + CHAR(10)
+ "@w_errtype tinyint ," + CHAR(13) + CHAR(10)
+ "@w_rollb tinyint ," + CHAR(13) + CHAR(10)
+ "@zpc_pid int ," + CHAR(13) + CHAR(10)
+ "@w_ferr int ," + CHAR(13) + CHAR(10)
+ "@w_showerr int ," + CHAR(13) + CHAR(10)
+ "@wf_tot int ," + CHAR(13) + CHAR(10)
+ "@wf_tot2 int ," + CHAR(13) + CHAR(10)
+ "@wf_trg int ," + CHAR(13) + CHAR(10)
+ "@sqlstr NVARCHAR(3000) ," + CHAR(13) + CHAR(10)
+ "@parmdef NVARCHAR(2500) ," + CHAR(13) + CHAR(10)
-- here each trigger adds fields def. for tables inserted or deleted
-- write info about key
IF @@fs_iid IS NULL
BEGIN
SELECT @w_exe05a = " SELECT @w_pk = 0 "
END
ELSE
BEGIN
SELECT @w_exe05a = " SELECT @w_pk = 1 "
END
SELECT @w_exe05 = CHAR(13) + CHAR(10)
+ "BEGIN " + CHAR(13) + CHAR(10)
+ RTRIM(@w_exe05a) + CHAR(13) + CHAR(10)
+ " SELECT @w_cur_i = 'zpd_i_" + RTRIM(@w_tab) + "'" + CHAR(13) + CHAR(10)
+ " SELECT @w_cur_u = 'zpd_u_" + RTRIM(@w_tab) + "'" + CHAR(13) + CHAR(10)
+ " SELECT @w_cur_d = 'zpd_d_" + RTRIM(@w_tab) + "'" + CHAR(13) + CHAR(10)
+ " SELECT @w_recur = " + CHAR(13) + CHAR(10)
+ " isnull(trigger_nestlevel( object_ID(@w_cur_i) ) , 0) " + CHAR(13) + CHAR(10)
+ " + isnull(trigger_nestlevel( object_ID(@w_cur_u) ) , 0) " + CHAR(13) + CHAR(10)
+ " + isnull(trigger_nestlevel( object_ID(@w_cur_d) ) , 0) " + CHAR(13) + CHAR(10)
+ " IF @w_recur > 1 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " RETURN -- no call between dyn triggers" + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " -- TRIAL PERIOD in evaluation version " + CHAR(13) + CHAR(10)
+ " IF datepart(year, getdate()) > 2003 " + CHAR(13) + CHAR(10)
+ " AND datepart(month, getdate()) > 06 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " RAISERROR ('ZPTrigger Trial Period Expired ' , 16, 1) " + CHAR(13) + CHAR(10)
+ " ROLLBACK " + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " SELECT @w_nr_i = COUNT(*) FROM inserted " + CHAR(13) + CHAR(10)
+ " SELECT @w_nr_d = COUNT(*) FROM deleted " + CHAR(13) + CHAR(10)
+ " -- initialize err table line " + CHAR(13) + CHAR(10)
+ " delete from zpc_err where spid = @@spid " + CHAR(13) + CHAR(10)
+ " insert into zpc_err values (0 , @@spid) " + CHAR(13) + CHAR(10)
SELECT @w_exe08id =
" DECLARE " + RTRIM(@w_cur_dyn) + " CURSOR FOR SELECT " + CHAR(13) + CHAR(10)
SELECT @w_exe08i = " FROM inserted n " + CHAR(13) + CHAR(10)
SELECT @w_exe08d = " FROM deleted o " + CHAR(13) + CHAR(10)
SELECT @w_exe_open = " OPEN " + RTRIM(@w_cur_dyn) + CHAR(13) + CHAR(10)
SELECT @w_exe_cur =
" FETCH NEXT from " + RTRIM(@w_cur_dyn) + " INTO " + CHAR(13) + CHAR(10)
+ " " + RTRIM(@@fs_ftx) + CHAR(13) + CHAR(10)
SELECT @w_exe_cur_stat =
" select @wf_tot = @@fetch_status " + CHAR(13) + CHAR(10)
SELECT @w_exe_curu =
" FETCH NEXT from " + RTRIM(@w_cur_dynu) + " INTO " + CHAR(13) + CHAR(10)
+ " " + RTRIM(@fs_ftxo) + CHAR(13) + CHAR(10)
/* as SQLServer allows update of PK: always work with two cursors
IF @@fs_iid IS NOT NULL -- PK
BEGIN
-- Strings used only in no PK
SELECT @w_exe08u5 = ' '
SELECT @w_exe08u6 = ' '
SELECT @w_exe08u7 = ' '
-- do strings
SELECT @w_exe08u1 =
" " + RTRIM(@w_exe08id)
+ " " + RTRIM(@fs_seln)
SELECT @w_exe08u2 =
" , " + RTRIM(@fs_selo) + CHAR(13) + CHAR(10)
+ " FROM inserted n INNER JOIN deleted o on" + CHAR(13) + CHAR(10)
+ " " + @@fs_iid + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe_open)
SELECT @w_exe08u3 =
" " + RTRIM(@w_exe_cur)
SELECT @w_exe08u4 =
" ," + RTRIM(@fs_ftxo)
+ " " + RTRIM(@w_exe_cur_stat)
END
ELSE -- No PK"
BEGIN
SELECT @w_exe08u1 =
" IF @w_nr_i = 1 -- only 1 record " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe08id)
+ " " + RTRIM(@fs_seln) + CHAR(13) + CHAR(10)
SELECT @w_exe08u2 =
" ," + RTRIM(@fs_selo) + CHAR(13) + CHAR(10)
+ " FROM inserted n CROSS JOIN deleted o " + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe_open)
SELECT @w_exe08u3 =
" " + RTRIM(@w_exe_cur)
SELECT @w_exe08u4 =
+ " ," + RTRIM(@fs_ftxo) + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe_cur_stat)
+ " END " + CHAR(13) + CHAR(10)
+ " ELSE -- More than 1 record: two cursors " + CHAR(13) + CHAR(10) + " -- cautious approach: read inserted only " + CHAR(13) + CHAR(10)
+ " -- daring approach: read both !in use! " + CHAR(13) + CHAR(10) + " BEGIN " + CHAR(13) + CHAR(10)
*/
SELECT @w_exe08u5 =
" " + RTRIM(@w_exe08id)
+ " " + RTRIM(@fs_seln) + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe08i)
SELECT @w_exe08u6 =
" DECLARE " + RTRIM(@w_cur_dynu) + " CURSOR FOR SELECT " + CHAR(13) + CHAR(10)
+ " " + RTRIM(@fs_selo) + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe08d)
+ " " + RTRIM(@w_exe_open)
SELECT @w_exe08u7 =
" " + RTRIM(@w_exe_cur)
+ " " + RTRIM(@w_exe_cur_stat)
+ " OPEN " + RTRIM(@w_cur_dynu) + CHAR(13) + CHAR(10)
-- here update trigger reads deleted cursor + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe_curu) + CHAR(13) + CHAR(10)
-- SQLServer allows update of PK b)
-- + " END " + CHAR(13) + CHAR(10)
-- END -- SQLServer allows update of PK
-- SQLServer allows update of PK b) end
SELECT @w_exe20 =
" WHILE @wf_tot = 0" + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " -- print " + RTRIM(@@fs_idv) + CHAR(13) + CHAR(10)
+ " -- read every line " + CHAR(13) + CHAR(10)
+ " DECLARE " + RTRIM(@w_cur_trg) + CHAR(13) + CHAR(10)
+ " CURSOR FOR SELECT trg_cond, trg_msg, " + CHAR(13) + CHAR(10)
+ " m.msg_text , trg_action, trg_errtype, trg_rollb " + CHAR(13) + CHAR(10)
+ " FROM zpc_trg t " + CHAR(13) + CHAR(10)
+ " LEFT OUTER JOIN zpc_msg m ON t.trg_msg_cod = m.msg_cod " + CHAR(13) + CHAR(10)
+ " WHERE trg_status = 0 " + CHAR(13) + CHAR(10)
+ " and trg_tab = '" + RTRIM(@w_tab) + "'" + CHAR(13) + CHAR(10)
SELECT @w_exe20a =
" ORDER BY trg_level, trg_seq "
-- here each triggers chooses its corresponding type in table + CHAR(13) + CHAR(10)
SELECT @w_exe_cur_trg =
" FETCH NEXT FROM " + RTRIM(@w_cur_trg) + " INTO @w_sqlcon, " + CHAR(13) + CHAR(10)
+ " @w_msg, @w_msgm, @w_sqlact, @w_errtype, @w_rollb " + CHAR(13) + CHAR(10)
+ " SELECT @wf_trg = @@FETCH_STATUS" + CHAR(13) + CHAR(10)
SELECT @w_exe25 =
" OPEN " + RTRIM(@w_cur_trg) + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe_cur_trg)
+ " WHILE @wf_trg = 0 " + CHAR(13) + CHAR(10)
+ " BEGIN" + CHAR(13) + CHAR(10)
-- debug: NO comment (--) inside built strigs disables all further content
+ " -- Build the SQL string and param definition" + CHAR(13) + CHAR(10)
+ " SELECT @sqlstr = 'IF ( ' + rtrim(@w_sqlcon) + ')'" + CHAR(13) + CHAR(10)
+ " + ' BEGIN ' " + CHAR(13) + CHAR(10)
+ " + ' SELECT @str_idv = ' + '" + RTRIM(@@fs_idv) + "'" + CHAR(13) + CHAR(10)
+ " + ' PRINT @str_idv ' " + CHAR(13) + CHAR(10)
+ " + ' UPDATE zpc_err SET err = 1 WHERE spid = str(@zpc_pid)' "+ CHAR(13) + CHAR(10)
+ " + ' ' + RTRIM(ISNULL(@w_sqlact, ' ')) " + CHAR(13) + CHAR(10)
+ " + ' END' " + CHAR(13) + CHAR(10)
+ " + ' ELSE ' " + CHAR(13) + CHAR(10)
+ " + ' BEGIN ' " + CHAR(13) + CHAR(10)
+ " + ' UPDATE zpc_err SET err = 0 WHERE spid = str(@zpc_pid)' "+ CHAR(13) + CHAR(10)
+ " + ' END' " + CHAR(13) + CHAR(10)
+ " SELECT @parmdef = '@zpc_pid int, @str_idv varchar(100) ,'" + CHAR(13) + CHAR(10)
-- here each trigger adds fields for tables inserted/deleted
SELECT @w_exe28 =
" -- PRINT 'sqlstr= ' + RTRIM(@sqlstr) " + CHAR(13) + CHAR(10)
+ " EXECUTE sp_executesql @sqlstr, @parmdef, " + CHAR(13) + CHAR(10)
+ " @zpc_pid = @@spid, @str_idv = null, " + CHAR(13) + CHAR(10)
-- here each trigger adds params for fields of inserted/deleted + CHAR(13) + CHAR(10)
SELECT @w_exe30 =
" SELECT @w_ferr = err FROM zpc_err WHERE spid = @@spid" + CHAR(13) + CHAR(10)
+ " IF @w_ferr = 0 -- no error" + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " SELECT @w_showerr = 0" + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " IF @w_ferr = 1 -- error" + CHAR(13) + CHAR(10)
+ " -- get msg error from trg table or msg view (custom def)" + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " IF @w_msgm IS NULL " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " SELECT @w_msgo = @w_msg " + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " ELSE " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " SELECT @w_msgo = @w_msgm " + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " SELECT @w_showerr = @w_errtype" + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
-- warning error: if zpcokmsg = 1 and not fatal: accept the error
IF @@fs_okf = 0 -- if there is no field for msg acknowledgement, allways show error
BEGIN
SELECT @w_exe45 =
" IF @w_showerr = 1 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " SELECT @w_showerr = 2" + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
END
ELSE -- there is column, error is warning and okmsg > 0, no msg
BEGIN
SELECT @w_exe45 =
" IF @w_showerr = 1 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " IF ISNULL(@zpcokmsg, 0) > 0 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " SELECT @w_showerr = 0" + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " ELSE " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " SELECT @w_showerr = 2" + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
END
SELECT @w_exe50 =
+ " IF @w_showerr = 2 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " BREAK " + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe_cur_trg)
+ " END -- while trigger trg" + CHAR(13) + CHAR(10)
+ " IF Cursor_Status('local', '" + RTRIM(@w_cur_trg)+ "') >= 0 " + CHAR(13) + CHAR(10)
-- + " IF NOT(@w_showerr = 2) -- rollback closes cursor " + CHAR(13) + CHAR(10)
+ " BEGIN" + CHAR(13) + CHAR(10)
+ " CLOSE " + RTRIM(@w_cur_trg) + CHAR(13) + CHAR(10)
+ " END" + CHAR(13) + CHAR(10)
+ " DEALLOCATE " + RTRIM(@w_cur_trg) + CHAR(13) + CHAR(10)
+ " IF @w_showerr = 2 -- rollback is total, break again " + CHAR(13) + CHAR(10)
+ " BEGIN" + CHAR(13) + CHAR(10)
+ " BREAK" + CHAR(13) + CHAR(10)
+ " END" + CHAR(13) + CHAR(10)
+ " " + RTRIM(@w_exe_cur)
SELECT @w_exe90 =
" " + RTRIM(@w_exe_cur_stat)
+ " END -- while trigger dyn" + CHAR(13) + CHAR(10)
+ " CLOSE " + RTRIM(@w_cur_dyn) + CHAR(13) + CHAR(10)
+ " DEALLOCATE " + RTRIM(@w_cur_dyn) + CHAR(13) + CHAR(10)
+ " DELETE FROM zpc_err where spid = @@spid " + CHAR(13) + CHAR(10)
SELECT @w_exe90u =
-- SQLServer allows update of PK c)
-- " IF @w_pk = 0 AND @w_nr_i = 1 -- two cursors " + CHAR(13) + CHAR(10)
-- + " BEGIN " + CHAR(13) + CHAR(10)
+ " CLOSE " + RTRIM(@w_cur_dynu) + CHAR(13) + CHAR(10)
+ " DEALLOCATE " + RTRIM(@w_cur_dynu) + CHAR(13) + CHAR(10)
-- + " END " + CHAR(13) + CHAR(10)
-- SQLServer allows update of PK c) end
IF @@fs_okf = 1
BEGIN
SELECT @w_exe95 =
" IF ISNULL(@zpcokmsg, 0) = 1 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " UPDATE " + LTRIM(@w_tab) + " SET zpcokmsg = 0" + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
SELECT @w_exe95 = " "
END
SELECT @w_exe99 =
-- no distinction between warning and fatal
-- " IF @w_showerr = 1 " + CHAR(13) + CHAR(10)
-- + " BEGIN " + CHAR(13) + CHAR(10)
-- + " RAISERROR (@w_msgo , 11 , 1)" + CHAR(13) + CHAR(10)
-- + " END " + CHAR(13) + CHAR(10)
-- + " ELSE " + CHAR(13) + CHAR(10)
+ " IF @w_showerr = 2 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " RAISERROR (@w_msgo , 15 , 1)" + CHAR(13) + CHAR(10)
-- rollback work or not according to rollb"
+ " IF @w_rollb = 1 " + CHAR(13) + CHAR(10)
+ " BEGIN " + CHAR(13) + CHAR(10)
+ " ROLLBACK " + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ " END " + CHAR(13) + CHAR(10)
+ "END " + CHAR(13) + CHAR(10)
END
--
-- create triggers for insert, update and delete
-- diferences: tables/data fetched : inserted, inserted and deleted , deleted
IF @w_do_i = 1 -- create trigger for insert
BEGIN
-- print 'len exe30 ' + isnull(str(len(@w_exe30) ) , 'nul')
/* PRINT @w_exe00 + @w_exe00i
+ @w_exe01 + @w_exe01i + @w_exe03
+ @@fs_dcl + @w_exe05 + @w_exe08id
+ " " + @fs_seln
+ @w_exe08i + @w_exe_open + @w_exe_cur
+ @w_exe_cur_stat + @w_exe20
+ " AND trg_ins = 1"
+ @w_exe20a
+ @w_exe25
+ " + '" + @@fs_dcl + "'"
+ @w_exe28
+ " " + @@fs_dyn
+ @w_exe30 + @w_exe45 + @w_exe50 + @w_exe90 + @w_exe95 + @w_exe99
*/
EXEC (
@w_exe00 + @w_exe00i
+ @w_exe01 + @w_exe01i + @w_exe03
+ @@fs_dcl + @w_exe05 + @w_exe08id
+ ' ' + @fs_seln
+ @w_exe08i + @w_exe_open + @w_exe_cur
+ @w_exe_cur_stat + @w_exe20
+ " AND trg_ins = 1"
+ @w_exe20a
+ @w_exe25
+ " + '" + @@fs_dcl + "'"
+ @w_exe28
+ " " + @@fs_dyn
+ @w_exe30 + @w_exe45 + @w_exe50 + @w_exe90 + @w_exe95 + @w_exe99
)
END -- trigger insert
-----------------
IF @w_do_u= 1 -- create trigger for update
BEGIN
/* PRINT
@w_exe00 + @w_exe00u
+ @w_exe01 + @w_exe01u
+ @w_exe03
+ @@fs_dcl + ',' + @fs_dclo
+ @w_exe05
-- @w_exe08u1 + @w_exe08u2 + @w_exe08u3 + @w_exe08u4 +
+ @w_exe08u5 + @w_exe08u6 + @w_exe08u7
+ @w_exe20
+ " AND trg_upd = 1"
+ @w_exe20a
+ @w_exe25
+ " + '" + @@fs_dcl + ',' + @fs_dclo + "'"
+ @w_exe28
+ " " + @@fs_dyn + "," + @fs_dyno
+ @w_exe30 + @w_exe45
+ @w_exe50
+ " " + @w_exe_curu
+ @w_exe90 + @w_exe90u + @w_exe95 + @w_exe99
*/
EXEC (
@w_exe00 + @w_exe00u
+ @w_exe01 + @w_exe01u
+ @w_exe03
+ @@fs_dcl + ',' + @fs_dclo
+ @w_exe05
-- @w_exe08u1 + @w_exe08u2 + @w_exe08u3 + @w_exe08u4 +
+ @w_exe08u5 + @w_exe08u6 + @w_exe08u7
+ @w_exe20
+ " AND trg_upd = 1"
+ @w_exe20a + @w_exe25
+ " + '" + @@fs_dcl + ',' + @fs_dclo + "'"
+ @w_exe28
+ " " + @@fs_dyn + "," + @fs_dyno
+ @w_exe30 + @w_exe45
+ @w_exe50
-- update trigger reads deleted cursor
+ " " + @w_exe_curu
+ @w_exe90 + @w_exe90u + @w_exe95 + @w_exe99
)
END -- trigger update
-----------------
IF @w_do_d = 1 -- create trigger for delete NOTE: use col names of deleted without prefix p_
BEGIN
/* PRINT
@w_exe00 + @w_exe00d
+ @w_exe01 + @w_exe01d
+ @w_exe03 + @@fs_dcl
+ @w_exe05
+ @w_exe08id
+ " " + @fs_selo
+ @w_exe08d
+ @w_exe_open
+ @w_exe_cur
+ @w_exe_cur_stat
+ @w_exe20
+ " AND trg_del = 1"
+ @w_exe20a
+ @w_exe25
+ " + '" + @@fs_dcl + "'"
+ @w_exe28
+ " " + @@fs_dyn
+ @w_exe30 + @w_exe45 + @w_exe50 + @w_exe90 + @w_exe99
*/
EXEC (
@w_exe00 + @w_exe00d
+ @w_exe01 + @w_exe01d
+ @w_exe03 + @@fs_dcl
+ @w_exe05
+ @w_exe08id
+ " " + @fs_selo
+ @w_exe08d
+ @w_exe_open
+ @w_exe_cur
+ @w_exe_cur_stat
+ @w_exe20
+ " AND trg_del = 1"
+ @w_exe20a
+ @w_exe25
+ " + '" + @@fs_dcl + "'"
+ @w_exe28
+ " " + @@fs_dyn
+ @w_exe30 + @w_exe45 + @w_exe50 + @w_exe90 + @w_exe99
)
END -- trigger delete
--
SELECT @help_pk = REPLACE(@@fs_iid, 'n.' , '')
SELECT @help_pk = REPLACE(@help_pk, 'o.' , '@')
UPDATE zpc_trg SET trg_help_pk = @help_pk
WHERE trg_tab = @tab
AND trg_seq = @seq
FETCH NEXT from cur_ins INTO @tab, @seq, @trg_i, @trg_u, @trg_d, @w_text, @w_action
END -- while for cursor
CLOSE cur_ins
DEALLOCATE cur_ins
-- test error, rollback if there is an error
IF @w_err = 1
BEGIN
ROLLBACK
END
END -- prog trigger zpc_trg_mdyn

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO