DELETED RECORDS (1 Viewer)

[sacacompany]

hi i m just wondering if i can put all deleted records from table in a on table that has a column and insert deleted row into it by concatenating..i created a trigger but its giving error on column name!!!...below is the trigger.....
USE database;
GO

CREATE TRIGGER TenantDeleted ON RT_Tenant
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO DeletedRecord (ID, Record, delDate, UserID)
SELECT [ID],
CONVERT(VARCHAR(MAX),
ISNULL([TenantID], '') + ', ' +
ISNULL([Name], '') + ', ' +
ISNULL([Address], '') + ', ' +
ISNULL([City], '') + ', ' +
ISNULL([ContactPerson], '') + ', ' +
ISNULL(, '') + ', ' +
ISNULL([Mobile], '') + ', ' +
ISNULL([Phone], '') + ', ' +
ISNULL([METERNO], '')
) AS RECORD,
GETDATE(),
SYSTEM_USER
FROM deleted;
END;

 

[GPGeorge]

"Record" is the field name you are trying to use here? Why use a name that has other, very precise meaning in databases? Why not use a name that reflects what the table stores, "DeletedTenant", or if you want to include the word Record, "DeletedTenantRecord"?

I don't think "Record" itself is a reserved word, but I'd always avoid the chance of confusion.

The problem, I think is here: ISNULL(, '') + ', ' +

That free-floating comma seems wrong to me. What is the proper syntax for IsNull() in TSQL?

Without data to test on, it's hard for me to visualize, but that's where I would start validating.
There may be additional syntax errors.

(Edited to correct error and add additional context.)

 

[The_Doc_Man]

From a pragmatic viewpoint:

First, there is a school of thought that suggests you never delete data, you just mark it as "obsolete." So instead of moving records around, you flip a flag and your queries simply ignore any record for which OBSOLETE is TRUE. SQL engines given a proper WHERE clause will gladly not show you any obsolete records if that is what you wanted.

But let's assume you really wanted to get rid of the data from a particular table, because that was your original question. Then the next question comes up that you seem to not want the data (because you are deleting it) but you DO want it (because you are copying the whole record from the 'deleted' table.)

If you want to keep a separate record of deleted tenants, make a table that looks like your tenant record plus the date of deletion and the ID of the deleting user. Then just copy the whole record to a table with individual fields identical with the source record plus the extra fields you need for deletion date and user ID. Once you copy and then delete the tenant record, your total data should occupy close to the same space plus the two extra fields. Extracting meaningful data later would be SO much easier if you needed to do some sort of historical research.

Let's say you wanted to know if Joe Schlabotnik had ever been a tenant before. You could do a query that searched one relatively short field instead of a jumbled up mess of a concatenated field. Or if you wanted Joe's mobile number, it would be easy to extract even though it is near two other fields that would look like numbers. There would be no confusion over what you were actually keeping.

Since this is SQL server and not Access saving the data, I'm not nearly as familiar with it as with Access. However, there are options for compressing data that I have read about, so if you have these empty fields that you were worried about (with the ISNULL), they should take up very little space in the copied record.

 

[Isaac]

hi i m just wondering if i can put all deleted records from table in a on table that has a column and insert deleted row into it by concatenating..i created a trigger but its giving error on column name!!!...below is the trigger.....
USE database;
GO

CREATE TRIGGER TenantDeleted ON RT_Tenant
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO DeletedRecord (ID, Record, delDate, UserID)
SELECT [ID],
CONVERT(VARCHAR(MAX),
ISNULL([TenantID], '') + ', ' +
ISNULL([Name], '') + ', ' +
ISNULL([Address], '') + ', ' +
ISNULL([City], '') + ', ' +
ISNULL([ContactPerson], '') + ', ' +
ISNULL(, '') + ', ' +
ISNULL([Mobile], '') + ', ' +
ISNULL([Phone], '') + ', ' +
ISNULL([METERNO], '')
) AS RECORD,
GETDATE(),
SYSTEM_USER
FROM deleted;
END;

Please paste the exact error from sql server management studio in red
Secondly, double-click on that red font and let us know where your cursor takes you then.

 

[Isaac]

Agree with George, the isnull with the free floating comment is definitely wrong.
Note that getdate() is deprecated or nearly so, with current_timestamp being the recommended alternative, all else being equal