Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

create trigger 1

Status
Not open for further replies.
beechill4

beechill4

Technical User
May 10, 2005
207
IE
Hi,

I have a table called healthchecks
it has 2 fields, one call sentdate, the other called nextauditdate.

I want to create a trigger which updates the nextauditdate to sentdate plus 1 month whenever a new record is inserted.

help much appreciated.

Joe
 
Sort by date Sort by votes
Is there any link between these two tables?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
CREATE TRIGGER [TriggerName] ON [DatabaseName].[healthchecks]
FOR INSERT
AS

IF EXISTS (SELECT * FROM inserted)

BEGIN
update [DatabaseName].[healthchecks]
set nextauditdate=dateadd(month,1,sentdate)
where unique_id in (select unique_id from inserted)
END

make sure sentdate is not null
and if you have some unique_id on the the table.

Thanks



SQL Help Blog
 
Upvote 0 Downvote
hi,

i'm a bit confused with this line:
where unique_id in (select unique_id from inserted)

what does that mean?

Thanks
 
Upvote 0 Downvote
I am confused also along with beechill4. Why is it needed if you are dealing with one row at a time?

Jim
 
Upvote 0 Downvote
2 tables... stupid me.

EXISTS() is not necessary - either INSERT happens (and trigger fires) or not.

Multirow inserts are not a problem, as long as there is unique/primary key to catch on (unique_id).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
if i just want to update the row i have inserted or updated, how do i handle that?

 
Upvote 0 Downvote
I don't think you can. I believe you have to grab the value from the Inserted row and update the table based on some unique ID from the Inserted row. That is what nomadicalloy and vongrunt are trying to point out
 
Upvote 0 Downvote
ok!

my problem is that when i create the trigger with the line
where unique_id in (select unique_id from inserted)
i get an error....

Server: Msg 207, Level 16, State 3, Procedure joe, Line 8
Invalid column name 'unique_id'.
Server: Msg 207, Level 16, State 1, Procedure joe, Line 8
Invalid column name 'unique_id'.
 
Upvote 0 Downvote
Yes. He is not proposing to use that exact code. He is trying to tell you to substitue your unique column ID in the code.
 
Upvote 0 Downvote
Hi,

apologies if this sounds dumb!

each record in this table is uniquely identified by 2 columns:
servername
and
sentdate.

how would this work?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
1K
mikrom
mikrom
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
640
Mike Lewis
Mike Lewis
niall5098
  • Locked
  • Question
Create plan guide with date time parameters
Replies
3
Views
348
Chris Miller
Chris Miller
Niki_S
  • Locked
  • Question
Create view using two databases
Replies
4
Views
406
Chris Miller
Chris Miller
Auguy
  • Locked
  • Question
Delete Trigger Usage and Options 1
Replies
5
Views
439
Auguy
Auguy

Part and Inventory Search

Sponsor

Back
Top