February 19, 2009 at 1:13 pm
how to create a trigger that increments a column of a table by one whenever an insert occurs.
example:
CREATE TRIGGER [ENTITY SEQUENCE] ON dbo.CustomersTest
AFTER INSERT
thats as far as i got
February 19, 2009 at 1:20 pm
Don't create a trigger for that. Use an Identity column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2009 at 1:22 pm
SQL server is a little bit different than other languages like Oracle.
there is an Identity() property you can set on a column, which has the same effect that a sequence and trigger would have in Oracle...it auto numbers a column:
create table Customerstest (custid int identity(1,1) not null primary key, --start at 1, increment by 1
othercol varchar(30) )
insert into Customerstest(othercol)
select 'stuff' union all
select 'more stuff' union all
select 'the rest'
SQL server doesn't have sequences, so to do it manually, you'd need to get the max() that already exists int eh table, or keep track of the maxes in a different table...
stick with the built in identity to replace the sequence and trigger.
paul.starr (2/19/2009)
how to create a trigger that increments a column of a table by one whenever an insert occurs.example:
CREATE TRIGGER [ENTITY SEQUENCE] ON dbo.CustomersTest
AFTER INSERT
thats as far as i got
Lowell
February 19, 2009 at 1:29 pm
so your saying from my example:
for every new customer_id in a table, and i need to increment the sequence number by 1,
So if I have 33 as a sequence number and there are 5 new customers you should have 33 +1 which = 34 , then 34 + 1 which is 35 and so on till i reach 38. Dont use a trigger for this?
February 19, 2009 at 1:32 pm
well kind of...there is no such thing as a sequence number in SQL, but the Identity() has the equivalent for any given column...if you set the identity() on the column, you never send an insert statement that references that column....it gets it's value automatically..
use my example above, and select * from Customertest.
you'll see that the column automatically has values 1,2 and 3 for the three rows i inserted
Lowell
February 19, 2009 at 1:41 pm
ok what if five records are inserted, and i have another table customer value that reads this customertest table for the new records in the (custid) field and increments the other table (value) field = 33 by 1 for every new record. how would i write that?
February 19, 2009 at 1:42 pm
Another reason for using Identity() in SQL Server is that it automatically handles set based inserts like:
Insert Into customer
Select
*
From
old_customer
Where a trigger would have to implement looping to handle this situation because SQL Server triggers act on SETS not individual rows. The example I give above would cause an INSERT trigger to fire only 1 time no matter how many rows are inserted.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 19, 2009 at 1:46 pm
You mean update instead of insert, remember the customer table has 33 as the (value) field already ?
February 19, 2009 at 1:47 pm
paul.starr (2/19/2009)
ok what if five records are inserted, and i have another table customer value that reads this customertest table for the new records in the (custid) field and increments the other table (value) field = 33 by 1 for every new record. how would i write that?
create trigger MyTrigger on dbo.MyFirstTable
after insert
as
update dbo.MySecondTable
set Column = Column + 33 * (select count(*) from inserted);
It would look something like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2009 at 1:54 pm
is this correct
ALTER trigger [MyTrigger] on [dbo].[CustomersTest]
after insert
as
update dbo.EntitySequenceTest
set CurrentValue = EntityID + 1 * (select count(CustomerID) from dbo.CustomersTest)
do i need to take * out?
error Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Contacts' to data type int.
February 19, 2009 at 1:54 pm
paul.starr (2/19/2009)
ok what if five records are inserted, and i have another table customer value that reads this customertest table for the new records in the (custid) field and increments the other table (value) field = 33 by 1 for every new record. how would i write that?
[font="Verdana"]Sorry Paul. I think you are stuck in trying to treat SQL Server like Oracle. If you give us a bit of background on what you are trying to achieve and why, we can give you a better idea of how to go about it in SQL Server.
But I can tell you now: I have yet to find an example where using a trigger in SQL Server is the best solution.
[/font]
February 19, 2009 at 2:02 pm
two tables
Customers and EntitySequenceTest
Customers gets updated daily, and when new customerID's are added
the Entity sequence table column CurrentValue is set and when the new CustomerID is inserted in Customers, the CurrentValue field in Enttity SequenceTest is updated with new current number of CustomerID's but it has to be increment by one to coincide with a prefix update function column in Cusetomers . So 33 + 5 = 38 but it has to be current value incremented by 1
February 19, 2009 at 2:05 pm
paul.starr (2/19/2009)
two tablesCustomers and EntitySequenceTest
Customers gets updated daily, and when new customerID's are added
the Entity sequence table column CurrentValue is set and when the new CustomerID is inserted in Customers, the CurrentValue field in Enttity SequenceTest is updated with new current number of CustomerID's but it has to be increment by one to coincide with a prefix update function column in Cusetomers . So 33 + 5 = 38 but it has to be current value incremented by 1
[font="Verdana"]Why do customers get updated daily?
Why is the "Entity sequence table" set whenever there is a new customer?
What is the "prefix update function"? What is it used for?
[/font]
February 19, 2009 at 2:11 pm
i have an insert proc that runs nightly to check for new customers, and if it finds them it inserts them so the entity sequence table should be updated with current number of new customers+old customers, but they have to be inserted into entity sequence table one by one
and customerprefix function creates a new customer prefix similar to AAA or AAB or AAC and so on for each new customer, so the current value in entity sequence is equal to lets say 40 it takes 40 and runs it in the function to get the next customer prefix
February 19, 2009 at 2:19 pm
paul.starr (2/19/2009)
i have an insert proc that runs nightly to check for new customers, and if it finds them it inserts them so the entity sequence table should be updated with current number of new customers+old customers, but they have to be inserted into entity sequence table one by oneand customerprefix function creates a new customer prefix similar to AAA or AAB or AAC and so on for each new customer, so the current value in entity sequence is equal to lets say 40 it takes 40 and runs it in the function to get the next customer prefix
[font="Verdana"]Hmmm, a trigger isn't the approach I would use for that. I'd put all of the logic back into your "insert proc".
Do something like the following:
1. Get the total count of existing customers
2. Get the last assigned customer prefix
3. Get the list of new customers
4. Insert (as a set) the new customers, along with the correct customer prefix based on #1, #2 and a row_number() offset.
5. If you still need to, update the "entity sequence" with the number of new customers plus #1.
Entirely set based, all of the logic is in one place (not scattered across a procedure and a trigger), and you don't actually need to keep this "entity sequence" thingie, or at least not for this.
You may also be able to merge steps #3 and #4 into one step.
[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply