Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trigger creation

Posted on 2008-06-12
4
Medium Priority
?
562 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 2000 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

Q2 2017 - Latest Malware & Internet Attacks

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out our latest Quarterly Internet Security Report!

Question has a verified solution.

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

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…
One of the biggest threats facing all high-value targets are APT's.  These threats include sophisticated tactics that "often starts with mapping human organization and collecting intelligence on employees, who are nowadays a weaker link than network…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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