Structure change of virtual table

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

Transact-SQL (2000)

Structure change of virtual table

Author

Topic

DMP
Starting Member

41 Posts

Posted - 2006-08-29 : 03:20:37

Hi,
How can i change the structure of virtual table(Inserted,Deleted)
before the trigger execution ?
Example :
When trigger execute on pubs.dbo.discounts table the virtual inserted table strucure same as original table structure(Field=>discount decimal (4,2))
I want to change the inserted.discount field structure as decimal (5,2).

Is it possible if yes then How ?

regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 03:29:22

you can't change the structure of inserted and deleted. Why do you want to change it ?

DMP
Starting Member

41 Posts

Posted - 2006-08-29 : 04:12:16

Thanks,
Let i continue from my previos mail though actual requirment on another database.

I wish to update discount field with 15.6789 which structure (Decimal 4,2) but it will be stored 15.68 then I shall keep (15.68-15.6789)=.0011 to another table for optimizaton rounding off excess value.
I wish to store this excess value through the trigger but I cannot get the original value i,e 15.6789 from trigger execution.

Any idea to solve my goal...

regards,

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-08-29 : 04:14:48

Change the physical table column...

DavidM

Production is just another testing cycle

DMP
Starting Member

41 Posts

Posted - 2006-08-29 : 04:19:13

Sorry, There was some restriction to change the structure of physical table

regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 04:23:03

create a view out of the base table and change the structure in the view


create view view_name
as 
select convert(decimal(5,2), discount) as discount, . . .
from   table

And use instead of trigger to update the other table

DMP
Starting Member

41 Posts

Posted - 2006-08-29 : 06:09:05

Thanks,
But how can i get the the actual figure (15.6789 )
when update trigger is fiering on physical table (Discounts) ?

Not View Table update trigger.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources