Unable to preserve trigger

Appears when your trigger text references a column that has been renamed, deleted, or assigned a different data type.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '[column name]'.

- Unable to preserve trigger '[trigger name]'.

Explanation

A change to the table required the table to be recreated. When a table is recreated, the triggers attached to that table are automatically recreated as well.

Action

The recommended solution depends on the type of change made to the column referenced by the trigger.

To preserve a trigger that references a renamed column

• Rename the column to its original name and then save the table. This action will allow the table to be recreated. You can now rename the column, save the table again, and then edit the trigger to fix the renamed columns.

To preserve a trigger that references a deleted column

1. Expand the table that the trigger is attached to.
2. Right-click the trigger you want to change and choose Open from the shortcut menu.
3. Edit the trigger text and save the trigger.
4. Save the table or database diagram.

To preserve a trigger that references a column whose data type changed

1. In your database diagram, click the Save Change Script button in the Database Diagram toolbar. For more information, see Saving a Change Script.
2. Open the change script.
3. In Data View, expand the table that the incorrect trigger is attached to.
4. Delete the incorrect trigger. For details, see Deleting a Trigger.
5. Create a new trigger for the table. For details, see Creating a Trigger.
6. In the change script, select the trigger text. The set of statements that creates the trigger begins with a CREATE TRIGGER statement.
7. Copy the selected text into the Trigger window.
8. On the toolbar, click the Save button. This action saves the trigger in the database and adds the trigger to the table in the Tables folder.
9. Repeat steps 3 through 8 for each trigger you want to recreate.

See Also