Hi. I am working on a small project and I am using Java with a SQL Server 2000 back-end. I have written a trigger on one of the database tables which apparently is not doing anything.
Given below is the T-SQL code
CREATE TRIGGER [insertPreviousO wner] ON [dbo].[ITEquipmentMast er]
FOR UPDATE
AS
declare @previousOwner int
declare @prevOwner1 int
declare @prevOwner2 int
declare @prevOwner3 int
declare @prevOwner4 int
declare @prevOwner5 int
declare @serial varchar
set @previousOwner = (select currentOwner from deleted)
set @prevOwner1=(se lect previousOwner1 from deleted)
set @prevOwner2=(se lect previousOwner2 from deleted)
set @prevOwner3=(se lect previousOwner3 from deleted)
set @prevOwner4=(se lect previousOwner4 from deleted)
set @prevOwner5=(se lect previousOwner5 from deleted)
set @serial=(select serialNum from inserted)
if (@prevOwner1 is null)
begin
update ITEquipmentMast er set previousOwner1= @previousOwner where serialNum= @serial
end
else if (@prevOwner1 is not null and @prevOwner2 is null)
begin
update ITEquipmentMast er set previousOwner2= @previousOwner where serialNum= @serial
end
else if (@prevOwner2 is not null and @prevOwner3 is null)
begin
update ITEquipmentMast er set previousOwner3= @previousOwner where serialNum= @serial
end
else if (@prevOwner3 is not null and @prevOwner4 is null)
begin
update ITEquipmentMast er set previousOwner4= @previousOwner where serialNum= @serial
end
else if (@prevOwner4 is not null and @prevOwner5 is null)
begin
update ITEquipmentMast er set previousOwner4= @previousOwner where serialNum= @serial
end
For any update, it's supposed to check for a null previousOwner1, previousOwner2, previousOwner3, previousOwner4 or previousOwner5 field for the updated row. It should then insert the updated row's currentOwner value into the null previousOwner field.
Can anyone assist?
4  1260   ck9663 2,878
Recognized Expert Specialist
Hi. I am working on a small project and I am using Java with a SQL Server 2000 back-end. I have written a trigger on one of the database tables which apparently is not doing anything.
Given below is the T-SQL code
CREATE TRIGGER [insertPreviousO wner] ON [dbo].[ITEquipmentMast er]
FOR UPDATE
AS
declare @previousOwner int
declare @prevOwner1 int
declare @prevOwner2 int
declare @prevOwner3 int
declare @prevOwner4 int
declare @prevOwner5 int
declare @serial varchar
set @previousOwner = (select currentOwner from deleted)
set @prevOwner1=(se lect previousOwner1 from deleted)
set @prevOwner2=(se lect previousOwner2 from deleted)
set @prevOwner3=(se lect previousOwner3 from deleted)
set @prevOwner4=(se lect previousOwner4 from deleted)
set @prevOwner5=(se lect previousOwner5 from deleted)
set @serial=(select serialNum from inserted)
if (@prevOwner1 is null)
begin
update ITEquipmentMast er set previousOwner1= @previousOwner where serialNum= @serial
end
else if (@prevOwner1 is not null and @prevOwner2 is null)
begin
update ITEquipmentMast er set previousOwner2= @previousOwner where serialNum= @serial
end
else if (@prevOwner2 is not null and @prevOwner3 is null)
begin
update ITEquipmentMast er set previousOwner3= @previousOwner where serialNum= @serial
end
else if (@prevOwner3 is not null and @prevOwner4 is null)
begin
update ITEquipmentMast er set previousOwner4= @previousOwner where serialNum= @serial
end
else if (@prevOwner4 is not null and @prevOwner5 is null)
begin
update ITEquipmentMast er set previousOwner4= @previousOwner where serialNum= @serial
end
For any update, it's supposed to check for a null previousOwner1, previousOwner2, previousOwner3, previousOwner4 or previousOwner5 field for the updated row. It should then insert the updated row's currentOwner value into the null previousOwner field.
Can anyone assist?
Just by reading, it looks like you're trigger is recursing. Check the db setting (sp_dboption). You have an update trigger on ITEquipmentMast er and inside that trigger, you're updating it. It's also called nested triggers. Do you really have to follow this design/structure?
-- CK
Just by reading, it looks like you're trigger is recursing. Check the db setting (sp_dboption). You have an update trigger on ITEquipmentMast er and inside that trigger, you're updating it. It's also called nested triggers. Do you really have to follow this design/structure?
-- CK
Thanx CK. Have discovered that the problem lied in the declaration declare @serial varchar which was returning a single character only. Needed to specify the length of the @serial variable. Once again, thanks
Hi everyone.I developed a small java application. I am using jasper reports api for my reporting (exporting them to pdf). My problem is that when I install the application on another client and run it, the reports don’t come out. Can anyone please assist?
ck9663 2,878
Recognized Expert Specialist
Tons of reasons. Is everything working aside from the report? Can your apps connect to db?
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Joel Thornton |
last post by:
Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?
I understand that when using xp_cmdshell, the sql thread in question
waits until xp_cmdshell finishes what it's doing. Does this mean if
my xp_cmdshell call takes 30 seconds, that nobody else can insert to
this table until my xp_cmdshell and rest of the...
|
by: nosbtr1 |
last post by:
When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:
CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
|
by: Simon Holmes |
last post by:
Hi,
I am having trouble calling a UDF from a 'before update' trigger
whereas I have no problems calling it from the 'after update' trigger.
The trigger is as below :
CREATE TRIGGER foo
NO CASCADE
|
by: robert |
last post by:
typed this into the ibm.com search window, but didn't get anything
that looked like it would answer a question:
+trigger +faster +db2 +cobol
the question: are DB2 (390/v6, at the moment) triggers better
or worse (and how much so, of course) than the same functionality
coded in COBOL? assuming, of course, equal competence in the code.
|
by: Robert Fitzpatrick |
last post by:
On 7.4.2 I have a trigger that I want to update any existing boolean
values to false if a new one in that group is declare true by inserting
a new record or updating an existing record:
ohc=# CREATE OR REPLACE FUNCTION "public"."clear_common_groups" ()
RETURNS trigger AS'
ohc'# BEGIN
ohc'# IF NEW.common_area = ''t'' THEN
ohc'# UPDATE tblhudunits SET common_area = ''f'' WHERE
hud_building_id = NEW.hud_building_id;
| | |
by: Rick Casey |
last post by:
Hello,
I am trying to get a trigger to fire when a record is deleted that will
record the deleted record in a history file. Here is the trigger code:
CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS '
begin
INSERT INTO PEDIGREES_hist(
PedigreeID,
|
by: LyzH |
last post by:
Someone else had a question on how to emulate a mouse click. I tried posting
in that thread but I have something of a twist on this problem and I'm
really in trouble here! If I don't get help soon, I'm going to fail this
class and the class is already dragging down my GPA. I'm taking an
introductory visual basic class with an advanced visual basic book and
assignments (the department dropped the prerequisite and the class needs one....
|
by: Wojto |
last post by:
Hello!
Another day, another problem... :-)
I've got something like this:
CREATE TABLE A (
pk_A INT CONSTRAINT primarykey_A PRIMARY KEY
);
CREATE TABLE B (
|
by: DennBen |
last post by:
I am doing an update to set a field value = anothe field value (in the
same table) where it is not supplied. I'm handling this in the
trigger, but am getting deadlocks.
Do you see anything wrong with this that would cause deadlocking?
ALTER TRIGGER
ON .
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
