Solved

Trigger creation

Posted on 2008-06-12
4
552 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
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

SHARE your personal details only on a NEED to basis. Take CHARGE and SECURE your IDENTITY. How do I then PROTECT myself and stay in charge of my own Personal details (and) - MY own WAY...
If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now