Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

force input of another field when putting a value of 1 in field Expand / Collapse
Author
Message
Posted Monday, December 24, 2012 3:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 11:02 AM
Points: 4, Visits: 9
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.
Post #1399995
Posted Monday, December 24, 2012 3:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1399998
Posted Monday, December 24, 2012 3:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 11:02 AM
Points: 4, Visits: 9
all updates are being done through access which is connected to the sql db through odbc connection.
Post #1399999
Posted Tuesday, December 25, 2012 6:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 5:32 AM
Points: 55, Visits: 158
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


Post #1400116
Posted Tuesday, December 25, 2012 7:51 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1400127
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse