Menu
I got an error "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables", since there are ntext fields in original table, I had attached a trigger to.
Here is a solution: http://lazycodeprogrammer.blogspot.com/2009/08/how-to-use-inserteddeleted-with.html
But original (non modified) query is complex enough. What should I write instead of SELECT * FROM INSERTED, using JOIN operator, as it's recommended?
414
In DML triggers, the inserted and deleted tables are primarily used to perform the following: Extend referential integrity between tables. Insert or update data in base tables underlying a view. Test for errors and take action based on the error.
To create a trigger, we need to change the delimiter. Inserting the row into Table1 activates the trigger and inserts the records into Table2. To insert record in Table1. To check if the records are inserted in both tables or not.
It facilitates you to change the trigger definition without using a DROP TRIGGER statement. trigger_name: It specifies the name of the trigger that you want to create. AFTER INSERT or UPDATE or DELETE: It specifies that the trigger will be fired after the INSERT or UPDATE or DELETE operation is executed.
The real problem is you are trying to select columns in your inserted table that are of type ntext, text or image. This is not allowed in a trigger.
The real solution would be to change all your ntext, to nvarchar(max), text to varchar(max) and image to varbinary(max) as suggested by MS.
MS states that those types are deprecated and will be removed in future version.
Also, ntext are really slow as performance since there are no in-row data.
66
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
