Hello Eric, I got a problem like i am not able to write a delete trigger in which one field is text field. For updation and insertion i have written trigger successfuly which handles the text field. but for delete trigger i am not able to write it. i have written delete trigger with text field and there is no syntax error nothing. but when deletion happends in master table it returns null after trigger is fired.

master table: pple_t_person audit table : aud_pple_t_person field name that has text field: Notes

Delete Trigger is *******************

CREATE TRIGGER TI_PPLE_T_PERSON ON dbo.pple_t_Person after delete AS INSERT INTO AUD_PPLE_T_PERSON ( ck_RMS_ID , ix_First_Name , ix_Middle_Name , ix_Last_Name , Title , Sex , fk_Marital_Status , Phone_Work , Phone_Cell , Fax , Person_Type , fk_Call_Account_ID , ix_National_ID , Birth_Date , Deceased , Password , Allow_Web_Access , Second_Password , Mailing ,NOTES, ck_Date_Changed , Updated_By , Action ) select pk_RMS_ID , ix_First_Name , ix_Middle_Name , ix_Last_Name , Title , Sex , fk_Marital_Status , Phone_Work , Phone_Cell , Fax , Person_Type , fk_Call_Accounting_ID , ix_National_ID , Birth_Date , Deceased , Password , Allow_Web_Access , Second_Password , Mailing ,NOTES, getdate() , system_user , 'DELETE' from PPLE_T_PERSON where pk_rms_id in ( SELECT PK_RMS_ID FROM deleted I)

This trigger is syntax error free. but returns null when record is deleted from master table. What is solution. How to write delete trigger which handles text datatype. we are using sqlserver 2000.

NOTE: I tried with INSTEAD OF TRIGGER also, but thats inserting in to audit tables but its not getting deleted from master table

-- Anonymous, August 04, 2003

Answers

Raja,

From SQL Server 2000 Books Online:

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable. If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

In short, check the compatibility level (with sp_dbcmptlevel dbname) to make sure it is 80 or higher.

Hope this helps,

Eric

-- Anonymous, August 05, 2003