INVOICES Columns Data Constraints Grants IStatistics Triggers Flashback Dependen

Question

INVOICES Columns Data Constraints Grants IStatistics Triggers Flashback Dependencies Details Partitions Indexes ISQL ???? Actions.. NULLABLE DATA DEFAULT ) COLUMN-ID ? COMMENTS DATA TYPE NUMBER NUMBER COLUMN_NAME 1 INVOICE ID 2 VENDOR ID 3 INVOICE_NUMBER VARCHAR2(50 BYTE) No 4 INVOICE DATE 5 INVOICE TOTAL NUMBER(9,2) 6 PAYMENT TOTAL NUMBER(9,2) 7 CREDIT TOTAL 8 TERMS ID 9 INVOICE DUE DATE DATE 10 PAYMENT DATE DATE 1 (null) 2 (nul) 3 (null) 4 (null) 5 (null) 6 (null) 7 (null) 8 (null) 9 (nul) 10 (null) (null) (null) (null) (null) (null) 0 No No 0 Yes Yes DATE NUMBER(9,2) NUMBER 0 (null) (null) 0 No Yes (null)

Explanation / Answer

Assumptions : The date format you set is yyyymmdd as 19961019

The following trigger can help you get the desired result :

CREATE [dbo].[no_update]

ON [dbo].INVOICES

FOR UPDATE

AS

SET NOCOUNT ON

IF EXISTS (SELECT FROM dbo.INVOICES o JOIN deleted n ON n.PAYMENT_DATE< o.INVOICE_DATE)

BEGIN

RAISERROR('You cannot update bcoz invoice date is ahead of payment date')

ROLLBACK TRAN

SET NOCOUNT OFF

RETURN

END

SET NOCOUNT OFF

The theory: The trigger command can be used to prevent any potential changes in table. The SQL maintains two tables in memory, which is, of course not visible to user, but can be accessed. One of these table is the table on which any updation or changes have been made (donated by 'n' for new in our query), and the other one is the old config of table without any updation. We join these tables to see if we can get any record with the condition(PAYMENT_DATE < INVOICE_DATE). If this return any result, the EXISTS condition becomes true and thus the trigger is executed.

You can try this out by performing any query on the table.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.