/ ************************************************** ****************** File name: ** Copyright (c) 2003-2004 *********** Created: ** Date: 2004 -5-26 ** Modify: ** Date: ** Description: ** Affair_Read table Insert trigger to delete the corresponding message of the T_SYS_MESSAGE table ** version: v1.0 ** ----- -------------------------------------------------- -------------------- **************************** ************************************************ / CREATE TRIGGER [AFFAIR_READ_INSERTTRIGER] on dbo.Affair_read for Insert AS / * Declare the variable used by the trigger * / declare @doc_no varchar (30), - Mail sender @tcode varchar (250), - Mail reader @message_id int, - message ID @ename varchar (64), - Workman name @ecode varchar (24) - employee code / * Start transaction REMOVE_TOPSUBJECT * / BEGIN TRAN REMOVE_TOPSUBJECT / * Remove the corresponding field value of the insert record * /
/ * Assignment To Select * / Select @ doc_no = doc_no From Inserted Select @ tcode = tcode From Inserted / * save before inserting savepoint, prevent errors * / Save Tran my_Save1 / * Remove T_Sys_Message table corresponding message * / DELETE FROM T_Sys_Message WHERE Message_ID IN (SELECT Message_ID FROM (SELECT a.Message_ID FROM T_Sys_Message a, T_Sys_MessageInterface b WHERE a.Message_ID = b.Message_ID AND a.Takeover_Person=@tcode AND b.Column_Value=@doc_no) AS TMP) If @@ Error = 0 Commit Transaction Else Begin Rollback Transaction My_SAVE1 RAISERROR ('Database Error, Contact System Administrator', 16, 1) End / * ---------------- Code end - ------------------- * /
/ ************************************************** ****************** File name: ** Copyright (c) 2003-2004 *********** Created: ** Date: 2004 -5-26 ** Modify: ** Date: ** Description: ** A modification trigger for the ** private_AffAir table, is used to insert a message to the T_Sys_Message table ** version: v1.0 ** ------ -------------------------------------------------- -------------------- ************************************* ******************************************************** / CREATE TRIGGER [private_AFFAIR_UPDATETRIGER] on dbo.private_affair for Update AS / * Define the variables used by the trigger * / declare @creator varchar (24), - mail sender @Receiver varchar (250), - mail recipient, possible multiple ecode, use comma connection @starting_date datetime, - mail Send time @title varchar (60), - Mail title @ename varchar (64), - employee name @ecode varchar (24), - employee code @AFFAIR_ID VARCHAR (20), - mail number @Procedure char CHAR 1) - Mail status / * Declaration Temporary table * / declare @tmp table ([Messag_name] [nvarchar] (1000), [NVARchar] (255), [Takeover_Person] [varchar] (24), [Message_Date ] [DateTime], [Messagg_Type] [Smallint], [Marker] [varchar] (20), [Remark] [nvarchar] (500))
/ * Remove the corresponding field value of the insert record * /
/ * Update, DELETE Operation Sitting DELETED table provided by the INSERT operation using the INSERT operation using the system. * /
SELECT @AffAir_ID = Affair_id from Deleta
SELECT @ creator = creator FROM private_affair WHERE affair_id = @ affair_id SELECT @ receiver = receiver FROM private_affair WHERE affair_id = @ affair_id SELECT @ starting_date = starting_date FROM private_affair WHERE affair_id = @ affair_id SELECT @ title = title FROM private_affair WHERE affair_id = @ affair_id SELECT @ Procedure = [procedure] from private_affair where afffair_id = @fair_id
/ * Start transaction * / begin TRAN REMOVE_TOPSUBJECT
/ * Save before insertion, prevent errors * / save TRAN MY_SAVE1 / * Delivered mail recipient * / declare @Counter Smallint, - Counter @separator char (1) - Deliver Select @ counter = 0 Select @separator = ',' / * Get sender employee name * / select @ ename = ename from ecode where ecode = @ creator
/ * Determine whether this email is already in the message table * / if (NOT EXISTS (SELECT 1 from T_Sys_MessageInterface Where color_value = @ Affair_id)) and (@ procedure = 1) Begin While Len (@Receiver)> 0 Begin Select @ ecode = substring (@ receiver, 0, CHARINDEX (@ Separator, @ receiver)) / * insert data into T_Sys_Message table * / iNSERT iNTO T_Sys_Message (Messagg_Name, Issue_Person, Takeover_Person, Message_Date, Messagg_Type) VALUES (@ title, @ ename, @ ecode, @ Starting_date, '10 ')
/ * Insert T_Sys_MessageInterface the key value table * / INSERT INTO T_Sys_MessageInterface (Message_ID, Table_Name, Column_Name, Column_Value) VALUES (@@ IDENTITY, 'affair_read', 'doc_no', @ affair_id) SELECT @ Counter = @ Counter 1 SELECT @ Receiver = Substring (@ receiver, charindex (@ separator, @ receiver) 1, len (@Receiver) End end
IF @@ error = 0 Commit Transaction Else Begin Rollback Transaction MY_SAVE1 RAISERROR ('Mail Send Error, please contact the System Administrator', 16, 1) End / * --------------- --- End of the code ------------------- * /
Posted on May 27, 2004 17:05
