Trigger That Runs On Update

Feb 7, 2008

I'm new to SQL and am having some trouble figuring out Triggers. We use Microsoft Dynamics SL (formerly Solomon) for our accounting. We have a table that only has data in it while we're processing checks called PRCHECKTRAN. We'd like to build a report that uses the information in this table but it's only available until we "keep" the checks that we're printing. This creates an added step because we have to stop our Payroll process to go out and print the reports. It's been suggested to use a trigger to make a copy of the records in the PRCHECKTRAN table so we've got them after the checks are "kept." We're wanting something that's done behind the scenes so the user can continue to do things the way they always have while giving us access to the data past the payroll period.

Basically as soon as we calculate checks, PRCHECKTRAN is populated. Once the checks are accepted as good, PRCHECKTRAN is cleaned out. I think I need an update or insert trigger that copies all records over to another table that we'll create called something like PRCHECKTRAN_HOLD. I'm just not familiar enough with SQL to write it. Any direction you can provide would be greatly appreciated. Thanks.

View 3 Replies


ADVERTISEMENT

Update On Machine Runs Immediately, Update On Linked Server Takes 8 Minutes

Jan 2, 2008

What's up with this?

This takes like 0 secs to complete:

update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')

but From linked server this takes 8 minutes????!!!??!:

update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')


What settings or whatever would cause this to take so much longer from the linked server?

Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.

2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.

View 5 Replies View Related

Trigger Error When App Runs

Oct 5, 2005

I have SQL Server 2000, and our web application is in WebObjects.

I built a trigger on a table that indicates if certain fields in a record have been changed since the last time a report was run from the application.

This trigger runs fine through the Query Analyzer, and runs fine with a direct input through enterprise manager. However, when the WebObjects application tries to update the table, and error is thrown.

Is anyone familiar with a reason why an application would throw an error on an update, when the DB tools do not? If we disable the trigger, the application has no problem updating the table.

Here is the relevant portion of the trigger:

create trigger t_press_run_change
on dbo.press_run_line_item
for insert, update, delete
as

updatepress_run_line_item
setis_changed = 1
from deleted d
join press_run_line_item p on p.press_run_line_item_id = d.press_run_line_item_id
where(p.is_changed = 0 or p.is_changed is null)
AND ((isNull(p.print_quantity,0) <> isNull(d.print_quantity,0))
OR (isNull(p.spoilage_pct,0) <> isNull(d.spoilage_pct,0))
OR(isNull(p.ad_dimension_id,0) <> isNull(d.ad_dimension_id,0))
OR(isNull(p.quarter_fold_id,0) <> isNull(d.quarter_fold_id,0))
OR(isNull(p.max_qty_per_shipment,'') <> isNULL(d.max_qty_per_shipment,''))
OR(isNull(p.packaging_max_height,'') <> isNull(d.packaging_max_height,''))
OR(isNull(p.packaging_max_weight,'') <> isNull(d.packaging_max_weight,''))
OR(isNull(p.packaging_skids,'') <> isNull(d.packaging_skids,''))
OR(isNull(p.packaging_turns,'') <> isNull(d.packaging_turns,''))
OR(isNull(p.packaging_cartons,'') <> isNull(d.packaging_cartons,''))
OR(isNull(p.preprint_delivery_time,'') <> isNull(d.preprint_delivery_time,''))
OR(isNull(p.contact_id,0)<> isNull(d.contact_id,0))
OR(isNull(p.address_company,'')<>isNull(d.address_company,''))
OR(isNull(p.address1,'')<>IsNull(d.address1,''))
OR(isNull(p.address2,'')<>IsNull(d.address2,''))
OR(isNull(p.address_city,'')<>IsNull(d.address_city,''))
OR(isNull(p.address_state,'')<>IsNull(d.address_state,''))
OR(IsNull(p.address_zip,'')<>IsNull(d.address_zip,''))
OR(isNull(p.address_Country_id,'')<>IsNull(d.address_country_id,''))
OR(isNull(p.client_printer_id,'')<>IsNull(d.client_printer_id,'')))

View 2 Replies View Related

Trigger That Runs An Macro Access

Feb 14, 2005

Hi,

can I run a macro Access from a trigger ?

View 14 Replies View Related

SQL Server Admin 2014 :: Scheduled Job Runs Successfully But Table Does Not Update

Jun 25, 2015

I have a job scheduled that imports a table from a Oracle database. The job runs at 3am and reports success. But for some reason when i query the table to see how many records there are, I see the same row count as the day before (it should increase everyday- student enrollment). When i execute the package manually, the table updates fine.

SQL Server 2014/Win7 x64.

View 7 Replies View Related

Trouble With Update Trigger Modifying Table Which Fired Trigger

Jul 20, 2005

Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View 1 Replies View Related

A Procedure Runs Slow As A Job But Runs Fast Executed In A Query Window

Apr 23, 2008

Performance issue.


I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.

So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.

Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.

Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.

- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.

View 15 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

View 7 Replies View Related

Update Trigger Behaviour W/o A Trigger.

May 30, 2008

Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,

View 3 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Stored Procedure Just Runs And Runs

Oct 9, 2001

I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?

I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?

Any advice appreciated.

Derek


************************************************** ***********************
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.sp_ValidateAIGL
GO

CREATE PROCEDURE dbo.sp_ValidateAIGL
@IGLPeriodIDInt,
@IGLProgramIDInt

AS
/* ************************************************** ************************
Name:sp_ValidateIGL
Author:CL
Date:19-Jan-2001
Notes:
************************************************** ************************ */

--SET NOCOUNT ON

DECLARE@TaxYearChar(5),
@FrequencyChar(1),
@PeriodNo Int,
@ProgramLogIDInt

SELECT@TaxYear = TaxYear,
@PeriodNo = PeriodNo,
@Frequency = Frequency
FROMtbl_IGLPeriods
WHEREIGLPeriodID = @IGLPeriodID

SELECT @ProgramLogID = (SELECT ProgramLogID FROM tbl_SYSProgramLogs WHERE IGLProgramID = @IGLProgramID AND IGLPeriodID = @IGLPeriodID)

CREATE TABLE #IGLErrors
(
KeyFieldChar(24),
ErrorIDInt,
DescriptionVarChar(255)
)

-- *** Global Non Program Specific Data Errors ***
-- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed...
IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'

SELECT * FROM #IGLErrors

-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
2,
'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''),
'<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode),
'<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(ExpenseCode, '') IS NULL
ANDIGLProgramID = @IGLProgramID

SELECT * FROM #IGLErrors
-- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs
IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'

SELECT * FROM #IGLErrors
-- *** Program 1 and 2 errors ***
IF @IGLProgramID IN (1, 2)
BEGIN
-- CHECK - search for any records where the employee's COST_CENTRE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
1,
'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(CostCenter, '') IS NULL
ANDIGLProgramID = @IGLProgramID

SELECT * FROM #IGLErrors

-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in
-- the DEB_VIEW_APPOINTEE view...)
INSERT INTO #IGLErrors
SELECT DISTINCT
EMP_NO,
11,
'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
ANDPERIOD_NO = @PeriodNo
ANDTAX_YEAR = @TaxYear

SELECT * FROM #IGLErrors

-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table
INSERT INTO #IGLErrors
SELECT DISTINCT
ELEMENT,
12,
'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERE ELEMENT NOT IN
(
SELECT DISTINCT Element
FROM tbl_IGLElements
)
ANDPERIOD_NO = @PeriodNo

SELECT * FROM #IGLErrors

END

-- *** Add a error to indicate the number of errors ***
IF EXISTS (SELECT * FROM #IGLErrors)
INSERT INTO #IGLErrors
SELECT 0,
0,
'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!'
FROM#IGLErrors

-- Transfer the records to the ErrorsLog table ready for the user to view...
DELETE FROM tbl_SYSErrorsLog
INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description)
SELECT@ProgramLogID,
@IGLPeriodID,
KeyField,
ErrorID,
Description
FROM #IGLErrors
ORDER BY ErrorID

DROP TABLE #IGLErrors

SELECT *
FROM tbl_SYSErrorsLog
ORDER BY ErrorID

--SET NOCOUNT OFF

GO
GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public
GO

View 2 Replies View Related

Update Trigger To Update Another Table

Dec 17, 2001

I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)

create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as

if update(pstat)
begin
update med
set REC_FLAG = 2
from deleted dt
where med.uniq_id = dt.uniq_id
and dt.pstat = 2
and dt.spec_flag = 'kop'
end

View 1 Replies View Related

UPDATE Trigger Issue When Using UPDATE

May 30, 2008

I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.

Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating

ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS

SET NOCOUNT ON

IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)

View 7 Replies View Related

Update Trigger - Update Query

Jul 20, 2005

Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

INSTEAD OF UPDATE Trigger To Hold Update

Apr 3, 2008



hi!

i have a database with about 20 tables. i appended to each table a column "UpdatedOn", and i want to write a trigger to set the date of the update date in that column, using a trigger.

i want to avoid the trigger launching for the last column (UpdatedOn).

how can i detect the rows that changed, and modify only the update date/time?
i read something about TableName_Inserted and TableName_Deleted, but i would prefer to copy as generic as possible the data from there, meaning, not to write column names in my script.

another idea i thought about was to prevent the trigger executing if no other column except for UpdatedOn changed, but... i encounter some trouble, when i try to pass column name (as string) to UPDATE() function.(Error: Expecting ID or QUOTED_ID)

thank you in advance.

View 8 Replies View Related

Update Trigger

Apr 6, 2004

Hello All,
I've been adviced without much detail that the best way to handle updates is with an Update Trigger to write to an AuditTable.
I always thought it was better to avoid triggers.
I'm getting confused very fast in reading about triggers.
Could someone please point me in the right direction?

Thank you,
Tina

View 6 Replies View Related

UPDATE Trigger For PK

Dec 2, 2004

i am using address table to store the address of employees, agents, students; so can't use cascade update and delete. so i decided to use trigger but i got stuck here as well.
Here employeeid is the PK of employee id. so when it is updated, i want to update that id in corresponding address table as well.
so what i need is to find out the previous value of EmployeeID before updating to use in WHERE clause and new EmployeeID to use in SET clause.

<code>
CREATE TRIGGER [AddressUpdate] ON [dbo].[MCS_Employee]
FOR UPDATE
AS
IF UPDATE(EmployeeID)
BEGIN
UPDATE MCS_Address SET EmployeeID = 'i am stuck here; how to know the previous value?
WHERE EmployeeID = ???
END
</end>

ironically, PK in my table can be updateable as well.

View 2 Replies View Related

Update Trigger

Sep 1, 2005

Hello all. I have a table which is the exact replication of a different table. Now if an update takes place in the original table I want to copy that row into the new table without the update.I have this but I am not sure if it works.Create Trigger Content_Archive on content
For Update
As
Insert Into content_audit Select * From DeletedI dont know if Updated exists so I am using Deleted. It is returning both rows (before and after update). And the insert should take place as soon as an update takes place in any field in the content table.

View 3 Replies View Related

Trigger Update?

Feb 21, 2001

Hello, I have two tables, looks as follow.

TOPIC
------
TOPIC_ID
STATUS_ID
TOPIC


THREAD
-------
THREAD_ID
TOPIC_ID
STATUS_ID
THREAD
THREAD_DATE
NAME

I want to update STATUS_ID in TOPIC when I post a new THREAD.
I guess the best would be to use a trigger?
Don't know much how you write them so if someone please could help or point me in right direction.
My plan is to always show the updated STATUS_ID in TOPIC while I have history in the THREAD TBL.
I am using MS SQL 7.0

Thanks for a great site.

Linkan

View 2 Replies View Related

I Am Trying Do A Update With A Trigger?

Feb 22, 2001

Hello,

I am new to this and on my way to learning. I hope someone can help me with a trigger. I want to update status of one table when I insert a new threat in another. This is what I have come up with so far.

CREATE TRIGGER [trg_Update_Status] ON tForumThread
FOR INSERT
AS
BEGIN
UPDATE tJournalTopic (STATUS_ID)
SELECT STATUS_ID
FROM Inserted

Thanks,
Linkan

View 2 Replies View Related

Update Trigger

Jan 5, 2000

View 1 Replies View Related

(Update) Trigger Help

Dec 17, 2002

I need to be able to select the row or specific field that was updated in an update trigger. I dont have any time-stamp or before-after value columns to compare. Please help!

View 4 Replies View Related

TRIGGER FOR UPDATE

Aug 20, 2001

I HAVE TWO TABLES IN THE DATABSE T1 AND THE SECOND TABLE(T2) IS FOR AUDITING.
THE STRUCTURE OF BOTH THE TABLES IS SIMILAR WITH ONE MORE COLUMN 'STATUS' IN THE T2.
I HAVE ALREADY WRITTEN THE TRIGGERS FOR THE INSERT AND DELETE TRIGGERS SO THAT I CAN SAVE THE
RECORD WITH STATUS OF 'NEW INSERT' OR 'DELETE' OR 'UPDATE' IN THE AUDIT TABLE(T2).
HELP ME IN WRITING THE TRIGGER FOR UPDATE.
MY PROBLEM IS I DON'T KNOW HOW TO STORE THE UPDATED COLUMNS VALUES INTO VARIABLES AND SO THAT
I CAN STORE THE OLD VALUES INTO THE AUDIT TABLE(T2).
I M USING SQL SERVER 6.5, SO WE CAN NOT USE COLUMNS_UPDATED() IN THE TRIGGER.
PLEASE SUGGEST ME THE CODE SO THAT I CAN STORE THE OLD ROW IN THE TABLE(T1) TO TABLE (T2)
WITH STATUS 'UPDATE' IN THE T2.

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH




=============================
/*test trigger for insert status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_insert') and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_insert
GO

CREATE TRIGGER tri_t1_insert ON dbo.t1
FOR INSERT
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from inserted
insert into t2(stamp,name,status) values(@v1,@v2,'NEW INSERT')
end
GO
========================================
/*test trigger for delete status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_delete') and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_delete
GO

CREATE TRIGGER tri_t1_delete ON dbo.t1
FOR delete
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from deleted
insert into t2(stamp,name,status) values(@v1,@v2,'DELETE')

end

View 1 Replies View Related

Update Trigger

May 29, 2002

I have an update trigger on a table on my transactional database that inserts a row of data into another database (audit database)for any modification made on the transactional database.
So if i modify a row on tran db it will write the data modified as a new row in the audit db.

This works fine if I am updating only 1 row with each Update statement. However if I update more than 1 row (multiple rows) with the same Update statement, the update trigger only inserts the last row modified in the audit database. So I lose record of any other rows modified with the same update statement.

Is there a way by which i can change my Update trigger or something, so I get all the rows updated by 1 update statement inmy audit database???


Thanks a bunch,
Judy

View 4 Replies View Related

Update Trigger

Apr 9, 2001

Hello All,
How do I create an update trigger that only updates the record that is being updated?

TIA,
Terry

View 1 Replies View Related

Update Trigger Plz Help

Mar 4, 2005

well basically i have a table with 3 columns in a table called TEST like:

TEST1 TEST2 TEST3
------- ------- -------
NULL NULL NULL

these columns can allow nulls. What i whant to do with my trigger is do a after trigger and check after the load if a certain column is NULL place a X instead like a flag but only on the columns that are NULL how would i do this.

plz help

View 2 Replies View Related

Update Trigger - Old Value

Oct 14, 2005

Is there a way I can get the old value of a specific field when using an update trigger?
e.g. I want to use the condition 'If Update(Column) '
in odrer to create a logfile which stores the old and new value of a field.
The new value can be get from a Select from Inserted table.
Is there a way I can get the old value (before update) also??

Regards,
Manolis

View 1 Replies View Related

Update Trigger

Mar 7, 2006

I have created a table with the following columns Jobnumber varchar(20), weight real(4), freightcost money(8), trackingnumber vchar(50), comments varchar(2000) and voidid varchar(3)

I wrote a trigger that updates this data based on the voidid to update the package table as followed:
CREATE TIGGER [UPS] ON dbo.UPSSHIPMENT
FOR INSERT
AS
DECLARE @JOBNUMBER CHAR(20)
DECLARE @WEIGHT REAL(4)
DECLARE @FREIGHTCOST MONEY(8)
DECLARE @TRACKINGNUMBER CHAR(25)
DECLARE @SHIPMETHOD CHAR(50)
DECLARE @voidid char(2)



SELECT @JOBNUMBER=JOBNUMBER,
@WEIGHT=WEIGHT,
@FREIGHTCOST=FREIGHTCOST,
@TRACKINGNUMBER=TRACKINGNUMBER,
@SHIPMETHOD=SHIPMETHOD,
@VOIDID=VOIDID
FROM INSERTED



UPDATE PACKAGE
SET PACKAGE.WEIGHT = @WEIGHT,
PACKAGE.FREIGHTCOST = @FREIGHTCOST,
PACKAGE.TRACKINGNUMBER = @TRACKINGNUMBER,
PACKAGE.COMMENTS = @SHIPMETHOD
WHERE PACKAGE.JOBNUMBER = @JOBNUMBER
AND @VOIDID = 'N'



UPDATE PACKAGE
SET PACKAGE.WEIGHT = '',
PACKAGE.TRACKINGNUMBER = '',
PACKAGE.COMMENTS = 'UPS VOID',
PACKAGE.FREIGHTCOST = ''
WHERE PACKAGE.JOBNUMBER = @JOBNUMBER
AND @VOIDID = 'Y'

I am getting the following error see attached.
Any help would be great Thank you!

View 2 Replies View Related

Update Trigger

May 10, 2006

Hallo there,

I'm totally new to writing triggers, but managed to get a trigger to update a specific column (ufINCTcost) on change of another column (ufINCKm)

My problem is that the trigger performs this update on ALL rows, which makes it very slow. How can I get it to only update the column on the row where the change was made ?

My trigger looks as follows:


CREATE TRIGGER updateincidents ON [dbo].[_rtblIncidents]
FOR UPDATE, INSERT
AS
IF UPDATE (ufINCKm)
BEGIN
UPDATE dbo._rtblIncidents
SET ufINCTcost=dbo._rtblIncidents.ufINCKm+dbo._rtblInc idents.ufINCToll+dbo._rtblIncidents.ufINCParking+d bo._rtblIncidents.ufINCFlight+dbo._rtblIncidents.u fINCRental

Hope you can help !!

View 5 Replies View Related

Trigger On Update

Nov 11, 2006

Hi,

I've got 2 tables, EMPLOYEE and STORE

The EMPLOYEE table holds all the basic stuff you would want to know about an employee and it contains a STORE_CODE attribute that points to the same attribute in the STORE table (to keep track of the store they work at). The STORE table has the STORE_CODE attribute, a NUM_EMP attribute that keeps track of the number of employees at that store, and some other information that is of no relevance to the question.

I've written the following TRIGGER to update the NUM_EMP attribute in STORE everytime a row is inserted or deleted from EMPLOYEE. It works fine for inserts and deletes but I am clueless as how to make it work for updates (an EMPLOYEE transfers to another store). I appreciate any feedback and please feel free to tell where I've gone wrong so far.

Thanks!


CREATE TRIGGER [UPDATENUMEMP] ON [EMPLOYEE]
FOR INSERT,UPDATE,DELETE
AS

DECLARE @STORECODE INT
DECLARE @NUMEMP INT

/*DELETE CASE*/
IF (NOT EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED))
BEGIN
DECLARE DELETECURSOR CURSOR FOR
SELECTSTORE_CODE, COUNT(*) AS NUMEMP
FROMDELETED
GROUP BY STORE_CODE

OPEN DELETECURSOR

FETCH NEXT FROM DELETECURSOR INTO @STORECODE, @NUMEMP
WHILE(@@FETCH_STATUS = 0)
BEGIN
UPDATESTORE
SETNUM_EMP = NUM_EMP - @NUMEMP
WHERESTORE_CODE = @STORECODE
FETCH NEXT FROM DELETECURSOR INTO @STORECODE, @NUMEMP
END
CLOSE DELETECURSOR
DEALLOCATE DELETECURSOR
END

/*INSERT CASE*/
IF(EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED))
BEGIN
DECLARE INSERTCURSOR CURSOR FOR
SELECTSTORE_CODE, COUNT(*) AS NUMEMP
FROMINSERTED
GROUP BY STORE_CODE

OPEN INSERTCURSOR

FETCH NEXT FROM INSERTCURSOR INTO @STORECODE, @NUMEMP
WHILE(@@FETCH_STATUS = 0)
BEGIN
UPDATESTORE
SETNUM_EMP = NUM_EMP + @NUMEMP
WHERESTORE_CODE = @STORECODE
FETCH NEXT FROM INSERTCURSOR INTO @STORECODE, @NUMEMP
END
CLOSE INSERTCURSOR
DEALLOCATE INSERTCURSOR
END
GO

View 5 Replies View Related

Update Trigger

Apr 22, 2008

This is something I have limited experience on. I need to create an update trigger after insert on one table that updates a completely different production table. My development server is being re-done and I'm not sure when it will be back on line. I have created some audit tables using triggers but they a simple inserts into a new table.

I believe the trigger statement should look like this, any advice would be appreciated.

Thanks in advance.

CREATE TRIGGER OB$1InsertAudit ON OB$0001
AFTER INSERT
AS
Begin
Update AX$0001
Set AX$0001.receiveddate=getdate(), AX$0001.docreviewstatus=null where Ob$0001.objid=AX$0001.refobjid and OB$0001.recordtype=6 and
OB$0001.descriptor in('Ancillary Services Tracking Form',
'FCE Referral Request',
'Remain At Work',
'Voc Rehab Job Retention')

End

View 5 Replies View Related

Update Trigger

Jan 21, 2004

Hi,

If I have a situation where a particular SQL statement updates three records on a table and there is an Update trigger defined on the table, then how many times will the trigger fire - three times or one time.

Thanks in advance.
Raj

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved