Help with trigger - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with trigger

Author

Topic

Anacrusis
Starting Member

4 Posts

Posted - 2002-12-20 : 09:49:03

Hello, I'm trying to get a trigger to work and I'm having a bit of trouble.
I have a journal system, and when a user adds a new entry, I need to either upadate a record in another table or insert a record. Here's what I got so far


CREATE TRIGGER JournalEntriesAdd ON [JOURNAL_ENTRIES] 
FOR INSERT
AS
BEGIN
   Set NoCount On

   declare @MemberID int, @EntryDate char(14)

   select  @MemberID = MEMBER_ID,
               @EntryDate = ENTRY_DATE
   from JOURNAL_ENTRIES
   where ENTRY_ID = @@identity

   declare @NeedInsert int

   --find out if this user has a JOURNAL_USERS record created already
   select @NeedInsert = MEMBER_ID
   from JOURNAL_USERS
   where MEMBER_ID = @MemberID
  
   if @NeedInsert  = @MemberID
      begin
	update JOURNAL_USERS set LAST_ENTRY = @EntryDate
	where MEMBER_ID = @MemberID
      end

   if @NeedInsert <> @MemberID
      begin
	insert into JOURNAL_USERS (MEMBER_ID, LAST_ENTRY)
      	values (@MemberID, @EntryDate )
      end

end

what I'm trying to do is update the LAST_ENTRY field in the users table with the date of their last
entry. If this is the first entry they've made, a JOURNAL_USERS record won't exist and one needs to be inserted.

Existing records will get updated just fine, but I can't get it to insert a new record....

any help would be greatly appreciated
Thanks
-- Adam

The Internet ClubHouse
Come join the community

Edited by - Anacrusis on 12/20/2002 09:50:28

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-20 : 10:04:55

instead of
if @NeedInsert <> @MemberID
try
if @NeedInsert is null

should be obvious why - if not try a few tests with a null variable.

or better

if exists (select *
from JOURNAL_USERS
where MEMBER_ID = @MemberID)
begin
update JOURNAL_USERS set LAST_ENTRY = @EntryDate
where MEMBER_ID = @MemberID
end
else
begin
insert into JOURNAL_USERS (MEMBER_ID, LAST_ENTRY)
values (@MemberID, @EntryDate )
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources