Solved

Trigger creation

Posted on 2008-06-12
4
558 Views
Last Modified: 2010-04-21
What I want to do is:

When INSERT into V_OPPORTUNITY
  IF USER_N4 is NULL then USER_N4 = 0

Now I looked at some triggers in the database and they don't look so simple.  For example (NOTHING I WANT TO DO but just looking trying to figure out the format I see as one example something like:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [UPDATE_BANK_ACCOUNT] on [dbo].[BANK_ACCOUNT] for UPDATE as
 SET NOCOUNT ON
 DECLARE @nRcd INT
 DECLARE @O_ID VARCHAR(15)
 DECLARE @N_ID VARCHAR(15)
 SELECT @nRcd = 0
IF @nRcd = 0
BEGIN
 DECLARE BANK_ACCOUNT_UPD CURSOR LOCAL FOR select i.id, d.id from inserted i, deleted d where i.rowid = d.rowid
 OPEN BANK_ACCOUNT_UPD
 FETCH BANK_ACCOUNT_UPD INTO @N_ID, @O_ID
 WHILE (@nRcd = 0 and @@fetch_status <> -1)
BEGIN
  update CASH_DISBURSEMENT set BANK_ACCOUNT_ID = @N_ID where BANK_ACCOUNT_ID = @O_ID
  update CASH_RECEIPT set BANK_ACCOUNT_ID = @N_ID where BANK_ACCOUNT_ID = @O_ID
  update BANK_DEPOSIT set BANK_ACCOUNT_ID = @N_ID where BANK_ACCOUNT_ID = @O_ID
  update BANK_ADJUSTMENT set BANK_ACCOUNT_ID = @N_ID where BANK_ACCOUNT_ID = @O_ID
  FETCH BANK_ACCOUNT_UPD INTO @N_ID, @O_ID
END
 DEALLOCATE BANK_ACCOUNT_UPD
END
 IF (@nRcd <> 0) RAISERROR('VMFG-%d error in trigger UPDATE_BANK_ACCOUNT', 16, -1, @nRcd)
 IF (@nRcd <> 0 Or @@ERROR <> 0) ROLLBACK TRANSACTION


So I am FREAKING out how to make my simple little trigger!
0
Comment
Question by:MyDanes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21770255
create trigger trV_OPPORTUNITY ON
V_OPPORTUNITY
for insert
as
begin
update v
set USER_N4 = 0
from V_OPPORTUNITY  v
JOIN inserted i on v.primarykey = i.primarykey
where
i.USER_N4 IS NULL
end
0
 

Author Comment

by:MyDanes
ID: 21770390
Thanks - however when I click the 'blue checkmark' to check this it is fine but when i click 'execute' it gives me

Msg 207, Level 16, State 1, Procedure trV_OPPORTUNITY, Line 15
Invalid column name 'primarykey'.
Msg 207, Level 16, State 1, Procedure trV_OPPORTUNITY, Line 15
Invalid column name 'primarykey'.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21770422
primarykey is a field name you'll need to swap out w/ the field(s) from  your tables that uniquely identify the rows (the primary key)
0
 

Author Closing Comment

by:MyDanes
ID: 31466575
thanks as usual chapmandew - wish you could help with the new one I have open :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you thought ransomware was bad, think again! Doxware has the potential to be even more damaging.
This story has been written with permission from the scammed victim, a valued client of mine – identity protected by request.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question