Solved

Trigger creation

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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...
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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