March 12, 2012 at 8:34 am
I am working with an application that builds a group of records in our retail data base. I wanted to assign a taxid to the group of records that are automatically generated by the application. I thought that the trigger below would do it but now I think that the system may not be just simply inserting the group of records, but inserting and then updating them. Anyway, the taxid is always zero after this executes.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_TaxID]
ON [dbo].[Item] after INSERT, UPDATE AS
begin
declare @rows as int, @id as int, @taxid as int
declare i_csr cursor for
select Id from INSERTED
open i_csr
select @rows = @@cursor_rows
if @rows <> 0
begin
fetch next from i_csr
into @id
while @@fetch_status = 0
begin
select @taxid = 1
update item set taxid = @taxid whereitem.id = @id
fetch next from i_csr
into @id
End
End
Close i_csr
deallocate i_csr
End
March 12, 2012 at 8:46 am
All that's going to do is set taxid to 1 for all records inserted. You don't need to use a cursor, either - it's a bad idea from a performance point of view and especially so in a trigger. What are you trying to do - set the same taxid for all rows inserted in one batch, or set a totally unique id?
John
March 12, 2012 at 8:48 am
That is performance killer trigger!
You should avoid using cursors when coding in T-SQL. Especially for this sort of tasks.
We will need a bit more details from you about what are you trying to do.
In your "trigger" @taxid is only set once to 1, it doesn't look right to me...
March 12, 2012 at 8:49 am
I want to set taxid = 1 to all new records in this table. I also want the user to be able to change the taxid to something else if thats what they want to do. Some items are taxable others are not.
The taxid remains 0 after this trigger executes. Not one record has a 1 in it.
March 12, 2012 at 8:51 am
How do I update the inserted records without using a cursor? I thought that I had to use a cursor to reference the INSERTED records.
March 12, 2012 at 8:54 am
ALTER TRIGGER [dbo].[tr_TaxID]
ON [dbo].[Item] after INSERT, UPDATE AS
UPDATE item SET taxid = 1 WHERE item.id IN (SELECT id FROM inserted);
Done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 8:54 am
Something like this?
UPDATE item
SET taxid = 1
WHERE id IN (SELECT id FROM Inserted)
You can't - or at least shouldn't - have user input in a trigger. If users need to update the value, they should do it in a separate operation.
John
March 12, 2012 at 8:55 am
You should be able to update all rows at once, like so:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_TaxID]
ON [dbo].[Item]
after INSERT, UPDATE AS
UPDATE [dbo].[Item]
SET
taxid = 1
FROM [dbo].[Item] item
INNER JOIN inserted i ON
i.id = item.id
GO
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 12, 2012 at 9:01 am
jean 93062 (3/12/2012)
I want to set taxid = 1 to all new records in this table. I also want the user to be able to change the taxid to something else if thats what they want to do. Some items are taxable others are not.The taxid remains 0 after this trigger executes. Not one record has a 1 in it.
You can do this even without using a trigger. Define your column "id" with a default value of 1. If the user wants to change it, he can.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 12, 2012 at 9:04 am
GilaMonster (3/12/2012)
ALTER TRIGGER [dbo].[tr_TaxID]ON [dbo].[Item] after INSERT, UPDATE AS
UPDATE item SET taxid = 1 WHERE item.id IN (SELECT id FROM inserted);
Done.
Just small note:
If you do it as above, even when anyone will try to change taxid to something else than 1, you will still end up with 1 in the table, as your trigger is defined for INSERT and UPDATE.
Actually, you can change this trigger to be for INSERT only.
Or, do it as it should be done:
Define your taxid column to be not-nullable and with default value of 1.
March 12, 2012 at 9:11 am
Thank you. Much simpler.
March 12, 2012 at 9:13 am
Right, the user would be using the application to make a change and that function is not intended to be in the trigger.
March 12, 2012 at 9:14 am
I'll try that because I think the application is doing something else and overrulling the effects of the trigger. Even the simpler trigger is still producing no "1". Remains zero.
March 12, 2012 at 9:15 am
Yes, thank you. I will remove the "UPDATED". It was there for testing.
March 12, 2012 at 9:17 am
jean 93062 (3/12/2012)
I'll try that because I think the application is doing something else and overrulling the effects of the trigger. Even the simpler trigger is still producing no "1". Remains zero.
The trigger fires after anything that the app would do, so there's no way the app can override it. Only way that trigger can not fire is if the app is using some form of bulk insert as those don't fire triggers by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply