Get Value from INSERTED/DELETE tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get Value from INSERTED/DELETE tables

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-20 : 19:37:42

I'm trying to create a trigger to update a column status for an Audit(s) if it is unscheduled within two weeks before the next calendar quarter. I can get it to update correctly, but I want to create a table to keep track of what records were updated.

I am getting a "Invalid object name 'deleted'" error when I try to retrieve the ID. Below is my code

DECLARE @NextQDate smalldatetime
SET @NextQDate = (SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) +1 ,0))
UPDATE tbl_Audit
SET AuditStatus = 'Delinquent: Audit Not Scheduled'
WHERE DATEDIFF (dd,@NextQDate,GETDATE())>=-14 
AND AuditStatus = 'Pending Date'
DECLARE @AuditID int, @Status varchar(100)
SELECT @AuditID = a.Audit_ID, @Status = a.AuditStatus
FROM tbl_Audit a
INNER JOIN deleted d
ON a.Audit_ID = d.Audit_ID

Here is my table

	CREATE TABLE tbl_Audit
	(
	Audit_ID int identity(4120700,1),
	Group_Name varchar(100),
	ScheduledQuarter varchar(5) not null, 
	ScheduledYear smallint not null,
	ScheduleDate smalldatetime,
	Audit_Scope varchar (Max),
	AuditStatus varchar(100),
	Primary key clustered (Audit_ID),
	Unique (Group_Name, ScheduledYear, ScheduledQuarter)

What I am ultimately trying to do is that after an update to the status, insert a new record into a tbl_DelinquentAudits table

INSERT INTO tbl_DelinquentAudits (Audit_ID, StatusChangeDate, StatusType) VALUES (@AuditID, Getdate(), 'Unscheduled Audit')

Does anyone know why I'm getting the "Invalid object name 'deleted'" error?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-20 : 19:41:53

I don't see CREATE/ALTER TRIGGER in the code you posted. Show us your trigger code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-20 : 19:48:03

It says I cannot create the trigger because the table has a foreign key with cascading DELETE or UPDATE. Does that mean I need to alter the foreign key and remove the cascading?

CREATE TRIGGER myTriggerINSERT
ON tbl_Audit
INSTEAD OF UPDATE
AS
DECLARE @NextQDate smalldatetime
SET @NextQDate = (SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) +1 ,0))
UPDATE tbl_Audit
SET AuditStatus = 'Delinquent: Audit Not Scheduled'
WHERE DATEDIFF (dd,@NextQDate,GETDATE())>=-14 
AND AuditStatus = 'Pending Date'
DECLARE @AuditID int, @Status varchar(100)
SELECT @AuditID = a.Audit_ID, @Status = a.AuditStatus
FROM tbl_Audit a
INNER JOIN deleted d
ON a.Audit_ID = d.Audit_ID
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-20 : 21:16:08

Do you need it to be an "instead of" trigger? Why not just a normal trigger that then fires your insert?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-21 : 11:14:12

quote:
Originally posted by tkizer

Do you need it to be an "instead of" trigger? Why not just a normal trigger that then fires your insert?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Actually, now that I think of it, this isn't going to be a trigger. It actually needs to be a scheduled job to run every midnight. So I guess this job needs to
1. Update column status if the audit is unscheduled
2. Insert a record of the change into a table

This changes the status of the record and seems to be working correctly

DECLARE @NextQDate smalldatetime
SET @NextQDate = (SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) +1 ,0))
UPDATE tbl_Audit
SET AuditStatus = 'Delinquent: Audit Not Scheduled'
WHERE DATEDIFF (dd,@NextQDate,GETDATE())>=-14 
AND AuditStatus = 'Pending Date'

The problem I have is just trying to retrieve the values from the inserted/delete virtual tables.

DECLARE @AuditID int, @Status varchar(100)
SELECT @AuditID = a.Audit_ID, @Status = a.AuditStatus
FROM tbl_Audit a
INNER JOIN deleted d
ON a.Audit_ID = d.Audit_ID
INSERT INTO tbl_DelinquentAudits (Audit_ID, StatusChangeDate, StatusType) VALUES (@AuditID, Getdate(), 'Unscheduled Audit')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-21 : 11:25:01

You can't use the inserted/deleted trigger tables because you aren't using a trigger. They are only available from within a trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-21 : 11:27:39

quote:
Originally posted by tkizer

You can't use the inserted/deleted trigger tables because you aren't using a trigger. They are only available from within a trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Interesting....so would it work if I have the scheduled job to update the records and then create a trigger to only fire if the status change is equal to 'Delinquent: Audit Not Scheduled' and nothing else? Are there any other methods you'd recommend?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-21 : 11:33:03

That sounds okay.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-21 : 14:03:52

Okay I was wrong about my code. It doesn't update the proper records. I adjusted the code and it should work now. I am having trouble with the TRIGGER to insert a record though.


--Update status to 'Delinquent' if unscheduled audit is 2 weeks before next quarter. Then add a record to the tbl_DelinquentAudits
DECLARE @NextQDate smalldatetime --First day of next quarter
DECLARE @NextQuarter varchar(100)-- Q + [current quarter] + 1
DECLARE @CurrentYear smallint -- Current year
SET @NextQuarter= 'Q' + CAST ((SELECT DATENAME(qq, GETDATE()) +1) as varchar(100))
SET @CurrentYear = (SELECT DATENAME(yy, GETDATE()))
SET @NextQDate = (SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) +1 ,0))
--If Today's quarter is Q4, @NextQuarter = Q1 and @CurrentYear = @CurrentYear +1
IF (@NextQuarter = 'Q5')
BEGIN
	SET @NextQuarter = 'Q1'
	SET @CurrentYear = @CurrentYear +1
END
UPDATE tbl_Audit
SET AuditStatus = 'Delinquent: Audit Not Scheduled'
WHERE DATEDIFF (dd,@NextQDate,GETDATE())>=-14 
AND AuditStatus = 'Pending Date'
AND ScheduledQuarter = @NextQuarter
AND ScheduledYear = @CurrentYear
PRINT @NextQuarter
PRINT @CurrentYear


CREATE TRIGGER myTriggerINSERT
ON tbl_Audit
FOR UPDATE
AS
BEGIN
	IF UPDATE (AuditStatus)
	BEGIN
	DECLARE @AuditID int
	SELECT @AuditID = a.Audit_ID
	FROM tbl_Audit a
	INNER JOIN inserted i
	ON a.Audit_ID = i.Audit_ID
	WHERE a.AuditStatus = 'Delinquent: Audit Not Scheduled'
	INSERT INTO tbl_DelinquentAudits (Audit_ID, StatusChangeDate, StatusType) VALUES (@AuditID, Getdate(), 'Unscheduled Audit')
END
END
GO

PROBLEM:When multiple records are updated, the trigger only inserts one record. The trigger fires on every update; it should only fire when the updated AuditStatus is equal to 'Delinquent: Audit Not Scheduled'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-21 : 14:06:25

INSERT INTO tbl_DelinquentAudits (Audit_ID, StatusChangeDate, StatusType)
SELECT a.Audit_ID, GETDATE(), 'Unscheduled Audit'
FROM tbl_Audit a
INNER JOIN inserted i
ON a.Audit_ID = i.Audit_ID
WHERE i.AuditStatus = 'Delinquent: Audit Not Scheduled'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-21 : 14:22:43

It seems to be working! Thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-21 : 14:46:01

You're welcome. The key part was using i.AuditStatus instead of a.AuditStatus in the WHERE clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-23 : 05:39:15

quote:
Originally posted by tkizer

You can't use the inserted/deleted trigger tables because you aren't using a trigger. They are only available from within a trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

In versions starting from 2005, they are available with OUTPUT clause

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-09-23 : 06:42:27

"PROBLEM:When multiple records are updated, the trigger only inserts one record"

Note that your trigger only fires once per batch, it does not fire "per record", so you need to code it (as Tara has done) to handle all rows in INSERTED table, rather than assuming there is only one

If you are writing a trigger to handle only one record, and are sure there will never me multiple records in the abtch (I think that's a bad idea ... but people tell me they do do it!) then at least put:

IF (SELECT COUNT(*) FROM INSERTED) > 1 RAISERROR ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources