December 24, 2012 at 3:33 pm
I have a field to indicate a delete which will be a text char of "1" in a field called deleterecord. Deleterecord has a default value of 0. This field is a text field char (1) and can only have the value of 0 or 1 with a FK lookup on another table. When the user changes the 0 to a 1, I want to force them to add the reason for the delete in another field called deletereason. I was thinking a trigger would work but being new to sql, not sure how to set this up. The table is called masterunit.
December 24, 2012 at 3:37 pm
I am assuming users insert/update/delete records using an application, not by directly writing SQL statements. If so, this is something that should be handled in the application.
December 24, 2012 at 3:51 pm
all updates are being done through access which is connected to the sql db through odbc connection.
December 25, 2012 at 6:52 pm
There are diffrent approaches in sql to do this. Triggers, output clause or the the parameter value can be determined if it will save to your deleted messages tables.
If you are using access, here is the sample code in sql with minimal conversion to access code. Just convert it. 🙂
if action = 1
begin
update masterunit
set deletedrecord = 1
insert into deletereason (reason)
values ("your reason")
end
December 25, 2012 at 7:51 pm
You can avoid inadvertant DELETEs or UPDATEs that mark the record as deleted (without a reason) like this:
CREATE TABLE MyTable
(MyPK INT PRIMARY KEY
,othercolumn VARCHAR(10)
,deleterecord CHAR(1) DEFAULT('0') CHECK (deleterecord IN ('0', '1'))
,deletereason VARCHAR(200) DEFAULT(NULL))
GO
CREATE TRIGGER MyTable_delete ON MyTable
INSTEAD OF UPDATE, DELETE
AS BEGIN
UPDATE t
SET deleterecord = i.deleterecord
,deletereason = i.deletereason
,othercolumn = i.othercolumn
FROM MyTable t
INNER JOIN INSERTED i ON t.MyPK = i.MyPK
WHERE (i.deleterecord = '1' AND i.deletereason IS NOT NULL) or i.deleterecord = '0'
END
GO
INSERT INTO MyTable (MyPK)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
SELECT * FROM MyTable
DELETE FROM MyTable WHERE MyPK = 1
UPDATE MyTable SET othercolumn = 'EXAMPLE' WHERE MyPK = 1
UPDATE MyTable SET deleterecord = '1' WHERE MyPK = 2
UPDATE MyTable SET deleterecord = '1', deletereason = 'MY CHOICE' WHERE MyPK = 3
SELECT * FROM MyTable
DROP TRIGGER MyTable_delete
DROP TABLE MyTable
However you will have to check in your application that a delete action (mark deleted) includes a reason because it is not easy in a trigger to return an error back to the client telling you when the mark deleted action doesn't include a reason.
I suppose you could raise an error and then check for it in the client but I've never tried that.
Also, you'll need to add additional columns to the SET within the UPDATE of the trigger, to also apply any other columns' new values (as obtained from the INSERTED psuedotable) as I have done with the othercolumn column.
I was also a bit weirded out by your suggestion that your assigning a FK check constraint to the deleterecord column when all you need is a simple CHECK constraint like the one I did above.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply