I have 2 tables as below:
Table1: Itemlists
Fields: ItemName, ItemCode
ex:
ItemName ItemCode
-------------- -------------
Product X X
Product Y Y
Table2: Product X
Fields: autono, ProdID, Condition, ExpiredDate, OtherInfor
I wan to have the data for Product X as below:
Autono ProdID Condiiton ExpiredDate OtherInfor
---------- --------- ------------- ---------------- ----------------
1 X00001 Good 02/02/2007 xxx
2 X00002 Good 12/02/2007 yyy
I created a triigger as below:
----------------------------------------
ALTER TRIGGER Trigger_itemidinsert
ON dbo.ProductX
FOR INSERT
AS
Declare @autono int, @itemcode nvarchar(5)
Select @autono = (Select autono from Inserted)
Select @itemCode = (Select itemCode from itemlists where itemName ='Product X')
Update Product X Set ProdID = @itemcode + '@autono' where autono = @autono
this is the just a try to get the ProdID as "X1" i suppose
However, i receive the error msg as below:
Error msg: conversion failed when converting the nvarchar value 'X' to data type int.
I knew it's is convertion problem but anyone can give me advise.
I would appreciate if anyone can provide me example code to get this : prodID : X00001 instead of X1
|
| Author: Gangarani Rajendran 11 Jul 2006 | Member Level: Silver | Rating:  Points: 2 |
Hi,
You have to use Cast(@autoint as varchar)
To get prodid like X00001, you have to use the string functions,
Select @autono = (Select autono from Inserted)
Select @itemCode = (Select itemCode from itemlists where itemName ='Product X')
Update Product X Set ProdID = @itemcode + Right('00000' + Cast(@autono as Varchar), 5 ) where autono = @autono
Regards,
Gangarani Rajendran
|
| Author: Tejinder Singh Barnala 24 Dec 2008 | Member Level: Gold | Rating: Points: 0 |
Gangarani is right
Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/
|
| Author: Roshan R Mhatre 15 Jan 2009 | Member Level: Gold | Rating: Points: 4 |
there is two database with name DB11 and DB22
DB11 Contain table with Name table1 with column empid,empno,name,Salary
DB22 Contain table with Name table2 with column empid,empno,name,Salary
use DB11
Select empid,empno,name,Salary from table1
select empid,empno,name,Salary from DB22..table2 tab2
Select table1.empid,table1.name,table1.salary
from table1,DB22..table2 tab2
where tab2.empid = table1.empid order by table1.name
Hope this will Help U alot
Best Of Luck!
|