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.
| Author |
Topic |
|
lloydtlee
Starting Member
6 Posts |
 Posted - 2002-09-01 : 22:00:13
|
| We are currently migrating from Oracle to MS SQL. One of our task is converting PL/SQL Triggers into MS SQL Triggers.I have a problem changing the inserted column in MS SQL.In Oracle, they have the :new.[columnname] which is the column of a temporary table like INSERTED or DELETED in SQL Server.In Oracle, they can change the value of that column in a trigger.How can I do it in SQL SERVER.lloydtlee |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-02 : 08:13:02
|
| Use the primary key to access the table - joining to the inserted table and update that.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
lloydtlee
Starting Member
6 Posts |
Posted - 2002-09-03 : 00:43:48
|
| I mean changing the value of the logical table INSERTED.I tried using this command:UPDATE INSERTEDSET USER = 'SYS'But the compiler said that I cannot change the logical tables INSERTED and DELETED.Is there a work-around.I am afraid that if I change the table itself, the trigger will fire again.lloydtlee |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-09-03 : 01:25:48
|
| You should update the actual table like nr suggests and you can use TRIGGER_NESTLEVEL (look it up in BOL) to prevent unwanted firing of triggers.----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-03 : 03:14:04
|
| You cannot change the inserted/deleted tables.Look at the recursive triggers option.Otherwise you can set a field in the trigger and test that field the next time the trigger fires to find if you are firing due to the insert or not.You also have a trigger_nestlevel() command which will tell you how many nested triggers are in action - but it won't tell you whether or not your trigger has fired.You could also design the system so that the trigger doesn't update the table it is acting upon.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
lloydtlee
Starting Member
6 Posts |
Posted - 2002-09-03 : 22:27:06
|
| tnxlloydtlee |
|
|
|
|
|
|
changing values of inserted columns in triggers