September 20, 2007 at 4:34 am
Hi,
I have an Update stmt like,
Update Table_name
Set Field3 = some value
where Field 1 in (1, 2, 3, 4, 5)
my trigger stmt will push a value in another table Table 2 with 5 stmts for each updation. There are no nulls. All Items match the in values.
the problem is, only one record gets inserted in table 2 but all values in Table1 gets updated. The same works if I use
update table_name
Set Field3 = some value
where field1 = 1
update table_name
Set Field3 = some value
where field1 = 2
update table_name
Set Field3 = some value
where field1 = 3
update table_name
Set Field3 = some value
where field1 = 4
Please tell me the reason. and Solution.. Should I use an UPDATE() in this context. Urgent..
September 20, 2007 at 4:42 am
Hi,
Please could you send the logic in your Trigger so that we can see if there is something wrong there.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 20, 2007 at 4:46 am
September 20, 2007 at 4:57 am
HI There,
That is very strange.
Here is the script I used to replicate the problem.
The trigger fire 100% every time.
--CERATE TABLES FOR TESTING
CREATE TABLE log_authors
(au_id INT );
CREATE TABLE authors
(
au_id int IDENTITY(1,1)
,Field1 VARCHAR(100)
);
--INSERT TEST DATA
INSERT INTO authors
(Field1)
SELECT 'a'
UNION
SELECT 'b'
UNION
SELECT 'c'
UNION
SELECT 'd'
UNION
SELECT 'e'
UNION
SELECT 'f'
UNION
SELECT 'g'
UNION
SELECT 'h'
GO
--CREATE TRIGGER
create trigger u_authors on authors for update
as
Insert into log_authors (au_id) select au_id from inserted
go
--DO UPDATE
Update authors
Set Field1 = 'Change'
where au_id in (1, 2, 3, 4, 5)
--CHECK RESULTS
SELECT *
FROM authors
SELECT *
FROM log_authors
Let me know if I am missing something.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 20, 2007 at 5:03 am
If you want to insert into the log all rows that were updated (regardless of whether something was changed or not), then your trigger should work. If you only want to insert rows where a change occurred, you need to join deleted and inserted tables and use "WHERE inserted.col1 deleted.col1" condition (assuming the column is NOT NULL, as you said).
I have no idea why your code does not do what you expect. Maybe you could post an example with how it should be and what happens...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply