November 22, 2004 at 7:47 am
Hi expert,
I need to create a update trigger, when there's any update to the table UPR100 (master table) or UPR200 from Great Plain (ERP system), the last column of the updated row will put 1 as updated indication in UPR100.
For example, the structure of UPR100 is like:
EmpID(PK), LastName, FirstName, Address, City, State, Zipcode, Phone1, Phone2, Email, Update_Ind
so, whenever any column has been modified for a particular Empid, the Update_Ind will set to 1, otherwise, it's null.
In Oracle, it's a row level trigger. I tried to write a trigger statement like this, if this looks okay?
Create Trigger Tg_UpdateIndication On UPR00100
AFTER UPDATE
AS
IF UPDATE (LASTNAME) or update (FRSTNAME) or update(ADDERSS1) or update (CITY) or update (Phone1)
BEGIN
Declare @EmpID char(15)
select @EmpID= EmployID from update
update UPR00100
Set Update_Ind=1
where EmployID=@EmpID
END
go
Thank you.
November 22, 2004 at 8:34 am
Assuming primary/unique key on UPR00100 is EmployID:
Create Trigger Tg_UpdateIndication On UPR00100
AS
IF UPDATE (LASTNAME) or update (FRSTNAME) or update(ADDERSS1) or update (CITY) or update (Phone1)
BEGIN
update UPR00100
set Update_Ind=1
from inserted
where (inserted.EmployID = UPR00100.EmployID)
END
November 22, 2004 at 8:44 am
OR
CREATE TRIGGER [tr_Update]
ON [dbo].[TableName]
AFTER UPDATE
AS
IF ((SELECT COUNT(PK) FROM INSERTED) > 0) AND ((SELECT COUNT(PK) FROM DELETED) > 0)
BEGIN
UPDATE TableName SET UPDATED = 'UPDATED'
WHERE TableName.PK = (SELECT PK FROM DELETED)
END
Steve
We need men who can dream of things that never were.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply