July 25, 2008 at 8:16 am
I am supporting an application that uses SQL Server 2005 as its DBMS. Recently, the vendor made a change in the application that has adversely affected some of my users. I have reported this to the software vendor, but any enhancement requests are, at best, weeks away.
Here's my scenario...
I have a configuration table with a single row.
The vendor has added a column to the table that the application reads and updates.
A NULL value is valid for this column.
What I'd like to do is allow the application to update the columns in the row of the table, but then have a trigger (or other mechanism) to reset the new column to NULL.
Table1
----------
column1
column2
column3
new_column
So, after each update, the new_column should be set back to NULL.
Any ideas?
Thanks!
July 25, 2008 at 8:24 am
Just to confirm then, when the 3rd party app writes the data back to this table, hen the trigger fires and changes the last column to Null from what ever it once was... If this is the case then I also did something similar... My code is below...
The code you are looking for is the last sections of the code ;
UPDATE dbo.EmailHistoryTest
SET dbo.EmailHistoryTest.EmailSent = 'TRUE'
WHERE dbo.EmailHistoryTest.EmailID = (SELECT EmailID From INSERTED)
So if i was to use your example, I would change the 'SET' command to NULL...
Full Code:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_ScriptEmail] ON [dbo].[EmailHistory]
AFTER INSERT
AS
BEGIN
DECLARE @EmailID int
DECLARE @EmailProfile nvarchar(100)
DECLARE@EmailTo nvarchar(500)
DECLARE@EmailCC nvarchar(500)
DECLARE@EmailBC nvarchar(500)
DECLARE@EmailSubject nvarchar(500)
DECLARE@EmailImportance nvarchar(10)
DECLARE@EmailAttachments nvarchar(500)
DECLARE@EmailBody nvarchar(MAX)
DECLARE@EmailQuery nvarchar(MAX)
DECLARE@EmailQueryFilename nvarchar(100)
DECLARE@EmailAttachToEmail nvarchar(1)
DECLARE @mailitem_id int
SELECT
@EmailProfile = EmailProfile,
@EmailID = EmailID,
@EmailTo = EmailTo,
@EmailCC = EmailCC,
@EmailBC = EmailBC,
@EmailSubject = EmailSubject,
@EmailImportance = EmailImportance,
@EmailAttachments = EmailAttachments,
@EmailBody = EmailBody,
@EmailQuery = EmailQuery,
@EmailQueryFilename = EmailQueryFilename,
@EmailAttachToEmail = EmailAttachToEmail
FROM INSERTED
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @EMailProfile,
@recipients = @EmailTo,
@copy_recipients = @EmailCC,
@blind_copy_recipients = @EmailBC,
@subject = @EmailSubject,
@importance = @EmailImportance,
@file_attachments = @EmailAttachments,
@body = @EmailBody,
@query = @EmailQuery,
@query_attachment_filename = @EmailQueryFilename,
@attach_query_result_as_file = @EmailAttachToEmail,
@mailitem_id = @mailitem_id OUTPUT;
UPDATE dbo.EmailHistoryTest
SET dbo.EmailHistoryTest.EmailSent = 'TRUE'
WHERE dbo.EmailHistoryTest.EmailID = (SELECT EmailID From INSERTED)
UPDATE dbo.EmailHistoryTest
SET dbo.EmailHistoryTest.MailItemID = @mailitem_id
WHERE dbo.EmailHistoryTest.EmailID = (SELECT EmailID From INSERTED)
END
If this doesn't help post your full code for us to look at...:D
July 25, 2008 at 8:34 am
i would be wary of adding a trigger to a third party table in the event that they send you a change but the trigger is never dropped and therefore causing problems in the third party tool.
however, for your requirement, a little simpler example of setting the column to null after an update would be the following trigger:
CREATE TRIGGER TRIG_U_TABLE1
ON dbo.T_TABLE1
AFTER UPDATE
AS
BEGIN
/*this will fire after the update occurs on the table and therefore setting the column back to null for all values as you stated in the requirement of the post*/
UPDATE T_TABLE1
SET NEW_COLUMN = NULL
END
GO
July 25, 2008 at 8:37 am
Are you certain this won't violate your support agreement with the vendor? Some vendors won't tolerate changes made to the database or the application. Also, modifications to third party applications can make upgrades a nightmare.
-- You can't be late until you show up.
July 25, 2008 at 8:49 am
a.thomson2 (7/25/2008)
Just to confirm then, when the 3rd party app writes the data back to this table, hen the trigger fires and changes the last column to Null from what ever it once was... If this is the case then I also did something similar... My code is below...The code you are looking for is the last sections of the code ;
UPDATE dbo.EmailHistoryTest
SET dbo.EmailHistoryTest.EmailSent = 'TRUE'
WHERE dbo.EmailHistoryTest.EmailID = (SELECT EmailID From INSERTED)
The only problem I would have with this solution is that it will fail if there is a batch update done because the subquery in the where clause will return more than 1 row. You would need to change the WHERE clause to
Where id IN (Select ID From inserted
Also note that doing select @variable = value from inserted does not handle a batch insert/update either and triggers fire on batches not once for each row affected.
Chuck's code will work, but do you really want to update every row each time a single row is updated? This is going to force a table scan or clustered index scan and probably lock the entire table. If it is a 10 row table that would be fine but 100's of thousands or millions of rows won't be.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 25, 2008 at 8:55 am
Thanks all...
It would appear that there is something else going on behind the scenes that I am not aware of.
After implementing the trigger some of the columns update and others do not.
I suppose those advising against placing triggers on 3rd party vendor tables have some experience doing so.
I just really wished I could have got this working, at least until the vendor addresses my issue.
Thanks!
July 25, 2008 at 8:57 am
jack is correct in what he saying about the many rows being updated for a possible single record update. however, i wrote the trigger to handle the req, a single row configuration table update, which is why the code is so simple
if there is more logic needed, like comparing the id against the inserted table, then i would make adjustments to the trigger to handle that scenario, probably just by adding a join to the inserted table on the id field.
i still go back to the original thought of being wary of updating a third party table though to apply this logic but you know your application better than we can explain.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply