November 10, 2016 at 8:28 am
Okay - I have an ERP application that I am trying to write a SQL trigger for.
I'm trying to write a record to a SQL Table whenever an existing record/field is updated by the end user.
Here's my script so far:
CREATE TRIGGER [dbo].[X_POPREQUIREDDATECHANGE] on [dbo].[POP10110]
FOR UPDATE AS
---SET NOCOUNT ON
IF UPDATE(REQDATE)
Insert INTO [dbo].[X_POPDateChange]
(PONUMBER, ITEMNUMBER,ITEMDESC,QTY,UOFM,REQUESTBY,OLDDATE,NEWDATE,CHANGEDATE)
SELECT PONUMBER, ITEMNMBR, ITEMDESC, QTYORDER, UOFM,
REQSTDBY, deleted.REQDATE, REQDATE, CAST(GETDATE() AS DATE) FROM deleted
where NONINVEN='0'
BEGIN
Update X_POPDateChange set NEWDATE=inserted.REQDATE
from inserted
END
GO
The trigger does work except that it creates/inserts a record every time a new PO Record is created. I know why it does it - it's because the ERP Application populates the table when the end user opens the PO Entry window with a default date in the REQDATE field - the end user then changes the system defaulted date and the trigger fires and populates the custom table.
My script needs to be altered to include some kind of 'IF EXISTS' logic - but I've never seen or used that type of logic before in my type scenario - I definitely need to populate a custom table but only if an existing record in the POP10100 table gets altered.............
Any thoughts would be appreciated.........
November 10, 2016 at 10:19 am
Assuming the PK of [dbo].[POP10110] is (PONUMBER, ITEMNMBR), try playing with the following:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[X_POPREQUIREDDATECHANGE]
ON [dbo].[POP10110]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
INSERT INTO [dbo].[X_POPDateChange]
(PONUMBER, ITEMNUMBER, ITEMDESC, QTY, UOFM, REQUESTBY, OLDDATE, NEWDATE, CHANGEDATE)
SELECT D.PONUMBER, D.ITEMNUMBER, D.ITEMDESC, D.QTY, D.UOFM, D.REQUESTBY, D.REQDATE, I.REQDATE, CURRENT_TIMESTAMP
FROM inserted I
JOIN deleted D
ON I.PONUMBER = D.PONUMBER
AND I.ITEMNMBR = D.ITEMNMBR
WHERE I.REQDATE <> D.REQDATE
--AND D.REQDATE <> 'system defaulted date'
AND D.NONINVEN = '0';
END
GO
November 15, 2016 at 12:56 pm
SQL programmers hate triggers, because they are procedural code and this is a declarative language. Anything you can do to avoid them is good. But I also see you do not know the difference between a row and a record or a column in a field. These are fundamental RDBMS concepts.
Unless you really want to go to jail auditing should be done with a third-party package that sits outside the database. That way accidents inside the database will not destroy the audit trail. But best of all, since this has come from an outside vendor you are not legally responsible (ROI these days means "risk of incarceration"). Part of my consulting is as an "expert witness" 😉
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 20, 2016 at 2:15 pm
Why do you need this part?
BEGIN
Update X_POPDateChange set NEWDATE=inserted.REQDATE
from inserted
END
I believe it can be simply removed.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply