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 |
|
cemplukcute2003
Starting Member
1 Post |
 Posted - 2005-05-05 : 05:24:24
|
| Hi, i need some help here.i'm currently new in MSSQL, basically i used to work with Oracle.i want to make a trigger for insert. but i want the trigger to execute before the data being inserted to the table. for example some condition like this : the user input data item such as : name, description, stock. the item_code will be generate by the trigger. the trigger will have to search the table and find the last data, increment the number then inserting to the item table.can i do that in MSSQL? and can someone give me a script as example?thank you very much... |
|
|
vijayakumar_svk
Yak Posting Veteran
50 Posts |
Posted - 2005-05-05 : 07:30:15
|
| Sure..Eg:create table ABC (id int,name varchar(10))Create trigger ABC_I on ABC Instead of InsertASBegin Declare @i int select @i=isnull(max(id),0)+1 from ABC print @i INSERT INTO ABC SELECT @i, Name FROM insertedEndinsert into ABC (Name) values('Vijay')-VJWork smarter not harder take control of your life be a super achiever |
 |
|
|
Sully
Starting Member
30 Posts |
Posted - 2005-05-05 : 15:53:51
|
| Unless I am misunderstanding the problem, couldn't ID be set as an identity column. Which would take care of incrementing the new record, without the need of a trigger.Stuck in neutral in a pedal to the metal world,Sully |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-05-05 : 17:43:49
|
| Identity column is the way to go. If you try to handle that with a home-grown solution like you're asking for, you're going to run into collisions when contention is high for these inserts. ie: to spids will get the same next_id and one of the inserts will generate a constraint violation. And if you wrap those 2 statements in a transaction to hold a lock to avoid the collision, you'll get deadlocks.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Trigger For Insert