Instead Of Trigger with update?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Instead Of Trigger with update??

Author

Topic

drewc
Starting Member

3 Posts

Posted - 2002-06-04 : 14:46:04

Almost there...

Converting my triggers to 'Instead Of' due to the use of text and ntext fields in SQL 2000 and cannot figure out how to access the data passed in from the original query by the update trigger. Here's the statement, can anyone tell me how I can access the updated field data in the inserted table ?? (inserted.FIELD_NAME does not work on an update)

Thanks much,
Drew
---------------------------------------------
Create Trigger "dbo.MNINSDTL_UPDATE_HISTORY"
On dbo.MNINSDTL
INSTEAD OF UPDATE

As

--THIS SECTION DOES NOT WORK!
--These values are passed from an SQL statement in an ASP page as a basic update statement.
--UPDATE VALUES IN MNINSDTL
UPDATE MNINSDTL SET
STATUS_ID = inserted.STATUS_ID,
CUSTOMER_NAME = inserted.CUSTOMER_NAME,
PRODUCT_NAME = inserted.PRODUCT_NAME,
TYPE_ID = inserted.TYPE_ID,
MGR_ID = inserted.MGR_ID,
FE_SERVICE_ORDER_NO = inserted.FE_SERVICE_ORDER_NO,
FE_CIRCUIT_ID = inserted.FE_CIRCUIT_ID,
LOCAL_ORDER_NO = inserted.LOCAL_ORDER_NO,
TCOMS_CIRCUIT_ID = inserted.TCOMS_CIRCUIT_ID,
NIC_TICKET_NO = inserted.NIC_TICKET_NO,
REQ_DATE = inserted.REQ_DATE,
CONF_NO = inserted.CONF_NO,
CONF_CODE = inserted.CONF_CODE,
LOCAL_CONTACT_NAME = inserted.LOCAL_CONTACT_NAME,
LOCAL_CONTACT_PHONE = inserted.LOCAL_CONTACT_PHONE,
LOCAL_CONTACT_CELL = inserted.LOCAL_CONTACT_CELL,
LOCAL_CONTACT_PAGER = inserted.LOCAL_CONTACT_PAGER,
VENDOR_NAME = inserted.VENDOR_NAME,
VENDOR_PHONE = inserted.VENDOR_PHONE,
VENDOR_CELL = inserted.VENDOR_CELL,
VENDOR_PAGER = inserted.VENDOR_PAGER,
WCOM_TECH = inserted.WCOM_TECH,
WCOM_TECH_BACKUP = inserted.WCOM_TECH_BACKUP,
EMAIL_RECIPIENTS = inserted.EMAIL_RECIPIENTS,
COMMENTS = inserted.COMMENTS,
LAST_UPDATE_BY = inserted.LAST_UPDATE_BY,
LAST_UPDATE_DATE = inserted.LAST_UPDATE_DATE
WHERE
REQ_ID = inserted.REQ_ID

---------------------------------------------
--THIS SECTION WORKS!
--INSERT VALUES IN MNINSDTL_HISTORY
INSERT MNINSDTL_HISTORY (
ACTION_TYPE,
ACTION_DATE,
REQ_VERSION,
REQ_ID,
STATUS_ID,
CUSTOMER_NAME,
PRODUCT_NAME,
TYPE_ID,
MGR_ID,
FE_SERVICE_ORDER_NO,
FE_CIRCUIT_ID,
LOCAL_ORDER_NO,
TCOMS_CIRCUIT_ID,
NIC_TICKET_NO,
REQ_DATE,
CONF_NO,
CONF_CODE,
LOCAL_CONTACT_NAME,
LOCAL_CONTACT_PHONE,
LOCAL_CONTACT_CELL,
LOCAL_CONTACT_PAGER,
VENDOR_NAME,
VENDOR_PHONE,
VENDOR_CELL,
VENDOR_PAGER,
WCOM_TECH,
WCOM_TECH_BACKUP,
EMAIL_RECIPIENTS,
COMMENTS,
CREATED_BY,
CREATED_DATE,
LAST_UPDATE_BY,
LAST_UPDATE_DATE,
DELETED)
SELECT
'UPDATED',
DATEADD(hh , -1, GETDATE()),
CASE WHEN MAX_VERSION.MAX_VERSION_ID IS NULL THEN 1
ELSE MAX_VERSION.MAX_VERSION_ID + 1 END,
REQ_ID,
STATUS_ID,
CUSTOMER_NAME,
PRODUCT_NAME,
TYPE_ID,
MGR_ID,
FE_SERVICE_ORDER_NO,
FE_CIRCUIT_ID,
LOCAL_ORDER_NO,
TCOMS_CIRCUIT_ID,
NIC_TICKET_NO,
REQ_DATE,
CONF_NO,
CONF_CODE,
LOCAL_CONTACT_NAME,
LOCAL_CONTACT_PHONE,
LOCAL_CONTACT_CELL,
LOCAL_CONTACT_PAGER,
VENDOR_NAME,
VENDOR_PHONE,
VENDOR_CELL,
VENDOR_PAGER,
WCOM_TECH,
WCOM_TECH_BACKUP,
EMAIL_RECIPIENTS,
COMMENTS,
CREATED_BY,
CREATED_DATE,
LAST_UPDATE_BY,
LAST_UPDATE_DATE,
DELETED
FROM inserted LEFT JOIN
(
SELECT
MAX(MNINSDTL_HISTORY.REQ_VERSION) AS MAX_VERSION_ID
FROM
MNINSDTL_HISTORY JOIN inserted ON MNINSDTL_HISTORY.REQ_ID = inserted.REQ_ID
)
AS MAX_VERSION ON 1=1

Edited by - drewc on 06/04/2002 14:49:58

Edited by - drewc on 06/04/2002 14:50:58

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-04 : 15:01:09

aren't you missing a FROM clause in your UPDATE statement?

<O>

drewc
Starting Member

3 Posts

Posted - 2002-06-04 : 15:26:50

Well ... guess that could be the problem then. I was mimicking my statement passed from the page which does not specify the optional from clause. I'll give it a try. Thanks Page47...

Drew

drewc
Starting Member

3 Posts

Posted - 2002-06-04 : 16:17:00

All set. Thanks for getting me back on track Page 47.

Drew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources