How would I use a trigger, in SQL Server 2000 to solve the following problem?

I need a trigger that will do the following:

As data is imported from a stored procedure into a local database I
want it to do two things:

1.	As my data is imported from the stored procedure, I want it to
change on-the-fly, to relocate the data.
-	I want the data that normally would be inserted into JOB_NO_TBC, to
insert into WO_KEY_TBC, row-by-row as it is inserted.
2.	 I want it to perform this task only if the date is > 11/23/2002

There are specific reasons that I just cannot change the insert clause
in the sql.  I’m also just wondering how the transact sql should look.

I want an example based on the following query:


IE. 

Here is an example of 1 part of my query:

INSERT INTO  dbo.CsgResultsTbcDts 
SELECT distinct * FROM OPENQUERY(csg, '
SELECT
              SYS_TBC
,             PRIN_TBC
,             AGNT_TBC
,             SUB_ACCT_NO_TBC
,             ADDR1_HSE
,             RES_CITY_HSE
,             MGMT_AREA_HSE
,             JOB_NO_TBC
,             TOT_UNITS_TBC
,             WO_RSN_TBC
,             CLS_OUT_RSN_TBC
,             ORIG_OPER_ID_TBC
,             IR_TECH_TBC
,             TIME_FLG_TBC
,             LS_CHG_DTE_TBC
,             LS_CHG_OPER_ID_TBC
,             LS_CHG_TERM_ID_TBC
,             WO_KEY_TBC
,             WO_STAT_TBC
,             WO_CLASS_TBC
,             WO_TYP_TBC
,             SCHED_CAT_TBC
,             DISPATCH_STAT_TBC
,	      HSE_KEY_HSE
,             PRIN_HSE
, 	      SYS_HSE
,	res_state_hse

FROM TBC_BASE, HSE_BASE
where
SUBSTR( WO_KEY_TBC, 1, 14) = HSE_KEY_HSE
and LS_CHG_DTE_TBC  >= SYSDATE -3
AND PRIN_TBC = PRIN_HSE
AND SYS_TBC = SYS_HSE
AND SYS_TBC = 8773')

WHERE SUBSTRing(WO_KEY_TBC, 1, 14) = HSE_KEY_HSE
AND (SUBSTRing(WO_RSN_TBC, 1, 2) = '42'
OR SUBSTRing(WO_RSN_TBC, 3, 2) = '42'
OR SUBSTRing(WO_RSN_TBC, 5, 2) = '42'
OR SUBSTRing(WO_RSN_TBC, 7, 2) = '42')
AND WO_STAT_TBC IN ('C', 'D' , 'X', 'H', 'O', 'R')
and not exists (select * from CsgResults_Master
where WO_KEY_WoAndTbc = WO_KEY_TBC and ls_chg_dte_WoAndTbc =
ls_chg_dte_tbc and wo_stat_woandtbc = wo_stat_tbc)


Thank you,

-Peter

---

Request for Question Clarification by mathtalk-ga on 25 Nov 2002 09:29 PST

Hi, pmclinn-ga:

Just to clarify, are the layouts of the two tables JOB_NO_TBC and
WO_KEY_TBC identical?  Are there any foreign key constraints on either
table?

regards, mathtalk-ga

---

Clarification of Question by pmclinn-ga on 26 Nov 2002 07:31 PST

JOB_NO_TBC is numeric and WO_KEY_TBC is varchar(20) also, there are no
key constraints as of yet.

---

Request for Question Clarification by mathtalk-ga on 27 Nov 2002 15:23 PST

Hi, pmclinn-ga:

Thanks for clearing that up.  You can create a trigger on the INSERT
event for the table which does what you want.  The only real
difficulty I forsee is that the trigger would be applied for all
inserts to that table, not just those which are the result of the
particular "import" stored procedure you are concerned with.  Of
course the trigger's logic can incorporate all of the limitations you
mention, but you should still consider whether this will cause other
insert actions on the table to have unanticipated consequences.

Let me know if you'd like me to post the CREATE TRIGGER syntax as an
answer.

regards, mathtalk-ga

---

Clarification of Question by pmclinn-ga on 01 Dec 2002 17:27 PST

I'm hoping the trigger will have an if then statement built into it. 
It doesn't matter if the trigger affects all data being inserted. 
That is what I want.

I need an example of this....

---

Request for Question Clarification by mathtalk-ga on 02 Dec 2002 06:36 PST

Hi, pmclinn-ga:

Thanks for the clarification.  I have developed and tested an example
trigger that I believe may be satisfactory for your purpose.

Without the specific definition of table dbo.CsgResultsTbcDts, any
example of a CREATE TRIGGER statement that I could provide would
necessarily be based on a table that I invent for the sake of
illustration.

For example, we might consider a table that has the basic features you
require:

- a field JOB_NO_TBC of numeric type

- a field WO_KEY_TBC of type varchar(20)

The trigger would have "if" logic regarding a date being after
11/23/2002, although I'm unclear from looking at your example which
date is meant.  Did you mean the system date, GETDATE( )?  Perhaps
LS_CHG_DTE_TBC ?  Is this of type datetime ?.

So I propose to use the following, for the sake of the example:

CREATE TABLE tblNeedsTrigger
( MY_1St_COL int,
  JOB_NO_TBC int,
  WO_KEY_TBC varchar(20),
  MY_CHG_DTE datetime 
)
GO

Note that the table contains no identity or computed columns, and no
columns of type timestamp, features that involve special
considerations for inserts.

The insert trigger logic would be to that if MY_CHG_DTE exceeds
11/23/2002, a value for WO_KEY_TBC will be inserted by converting
JOB_NO_TBC into a string; otherwise the values inserted will simply be
those provided by the insert action.

The trigger and its logic will apply to the existing stored procedure
and to all other INSERT actions on the table.

If such an example would be acceptable to you, please let me know. 
Clarify my thinking about the date condition if I have not correctly
interpreted it.  If my proposed example is not sufficient, then you
should perhaps define the target table as you wish to have it treated.

regards, mathtalk-ga

---

Clarification of Question by pmclinn-ga on 02 Dec 2002 18:03 PST

Your example would be fine, thanks.

---

Clarification of Question by pmclinn-ga on 02 Dec 2002 19:12 PST

If you really want to impress me have it delete a record if based on a
specific date range too.

Hi, pmclinn-ga:

Here is the sample trigger and supporting table/test records used.  To
accomplish your goal I used an "INSTEAD OF" trigger on the INSERT
action, a feature that become available in SQL Server 2000.

First the table creation language repeated for the sake of
completeness:

CREATE TABLE tblNeedsTrigger 
( MY_1St_COL int, 
  JOB_NO_TBC int, 
  WO_KEY_TBC varchar(20), 
  MY_CHG_DTE datetime  
) 
GO 

Second the actual trigger creation language:

CREATE TRIGGER trgInsteadOfInsert on tblNeedsTrigger
INSTEAD OF INSERT
AS
INSERT into tblNeedsTrigger
SELECT MY_1St_COL,
  JOB_NO_TBC,
  CASE WHEN MY_CHG_DTE > '11/23/2002'
    THEN CONVERT(varchar(20),JOB_NO_TBC)
    ELSE WO_KEY_TBC
  END,
  MY_CHG_DTE
FROM inserted

Now some test records.  This first test statement just causes the
indicated values to be inserted into the base table, because the
"change date" doesn't exceed the threshold:

INSERT into tblNeedsTrigger values
(1,412,'214','10/10/2000')

This next statement causes the modification of WO_KEY_TBC to take
place:

INSERT into tblNeedsTrigger values
(2,412,'214','10/10/2003')

so that WO_KEY_TBC stores '412' rather than '214'.

I'm not quite clear what you meant by having the trigger delete
records.  I'm fairly sure this can be done, but would need to have
more details.  If what you are thinking of is to prevent some records
from being inserted (rather than actually deleting records that
already exist), it can be easily accomplished by adding a date range
criterion as a WHERE clause in the body of the trigger.

regards, mathtalk-ga