Solved

Trigger creation

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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...
Getting hacked is no longer a matter or "if you get hacked" — the 2016 cyber threat landscape is now titled "when you get hacked." When it happens — will you be proactive, or reactive?
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

19 Experts available now in Live!

Get 1:1 Help Now