janwrage
asked on
Create SQL Trigger that denies INSERT
Hi!
I have a small table which is filled with timing data. Please see picture for details.
The values come from an application installed on all computers. It logs certain times (logon-time, overall runtime etc...) but sometimes the values are not correct, so I need to deny writing them to the db.
I. e. the difference between EndTime and LogonTime should not exceed 40 Minutes. Any higher value is incorrect and should not be written to the db.
Note: The column TimeLogonEnd is an nvarchar-type so it is easier to calculate the difference by using EndTime - LogonTime.
The sample contains two rows. The first one is ok, the second one exceeds 40 Minutes (EndTime - LogonTime) and should not be written to db.
I thought of writing a trigger that denies the insert, but I don't have the time to search google all day on how to do it, so I'm asking for help here.
Thanks and regards
Jan
Table-with-sample.png
table-definition.png
I have a small table which is filled with timing data. Please see picture for details.
The values come from an application installed on all computers. It logs certain times (logon-time, overall runtime etc...) but sometimes the values are not correct, so I need to deny writing them to the db.
I. e. the difference between EndTime and LogonTime should not exceed 40 Minutes. Any higher value is incorrect and should not be written to the db.
Note: The column TimeLogonEnd is an nvarchar-type so it is easier to calculate the difference by using EndTime - LogonTime.
The sample contains two rows. The first one is ok, the second one exceeds 40 Minutes (EndTime - LogonTime) and should not be written to db.
I thought of writing a trigger that denies the insert, but I don't have the time to search google all day on how to do it, so I'm asking for help here.
Thanks and regards
Jan
Table-with-sample.png
table-definition.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
you could also implement a check constraint to compare the 2 values ...
Since you are adding a constraint to CHECK a condition, a sql server CHECK CONSTRAINT works best, per angelIII's 2nd comment
alter table tbl
add constraint ck_valid_data check (
datediff(mi, logontime, endtime) <= 40 and logontime < endtime)
alter table tbl
add constraint ck_valid_data check (
datediff(mi, logontime, endtime) <= 40 and logontime < endtime)
ASKER
Thanks for the replies. I will try as soon as possible.
But I got another problem with the same table. Maybe there's a quick solution:
I have two rows in the table that are absolutely identical. SSMS says it cannot delete them, cause there are identical...I know, it's bad design, cause I have no PK...Is there a way to kick these rows? And if so, how to prevent this from happening?
But I got another problem with the same table. Maybe there's a quick solution:
I have two rows in the table that are absolutely identical. SSMS says it cannot delete them, cause there are identical...I know, it's bad design, cause I have no PK...Is there a way to kick these rows? And if so, how to prevent this from happening?
>beause I have no PK...
you need a PK
>Is there a way to kick these rows?
SET ROWCOUNT 1
DELETE yourtable WHERE ... --- put here enough conditions to match the 2 rows
SET ROWCOUNT 0
>And if so, how to prevent this from happening?
some constraints might do... it depends on what exactly is the data input routine, aka how much you can control on there...
you need a PK
>Is there a way to kick these rows?
SET ROWCOUNT 1
DELETE yourtable WHERE ... --- put here enough conditions to match the 2 rows
SET ROWCOUNT 0
>And if so, how to prevent this from happening?
some constraints might do... it depends on what exactly is the data input routine, aka how much you can control on there...
ASKER
Could you complete the above query please? As I'm a beginner on this subject I don't understand how to complete the where-part.
I'm using this query to get the duplicates as per MS: http://support.microsoft.com/kb/139444/en-us
SELECT username, starttime, logontime, endtime, count(*) as cnt
FROM tbltime
GROUP BY username, starttime, logontime, endtime
HAVING count(*) > 1
The result is attached.
duplicates.png
I'm using this query to get the duplicates as per MS: http://support.microsoft.com/kb/139444/en-us
SELECT username, starttime, logontime, endtime, count(*) as cnt
FROM tbltime
GROUP BY username, starttime, logontime, endtime
HAVING count(*) > 1
The result is attached.
duplicates.png
ASKER
And is it possible to "redesign" the table by adding a unique PK? And without loosing the existing data, of course...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>And is it possible to "redesign" the table by adding a unique PK?
yes, sure. you simply add a "identity" column, and then set that as PK.
yes, sure. you simply add a "identity" column, and then set that as PK.
ASKER
The first query throws an error:
Meldung 195, Ebene 15, Status 10, Zeile 2
'row_count' wird nicht als Name einer integrierten Funktion erkannt.
It say's row_count is not recognized as an integrated (or internal?) function. I'm using SQL2005.
Meldung 195, Ebene 15, Status 10, Zeile 2
'row_count' wird nicht als Name einer integrierten Funktion erkannt.
It say's row_count is not recognized as an integrated (or internal?) function. I'm using SQL2005.
please change:
;with tmp as
(SELECT username, starttime, logontime, endtime, rn=row_number() over (partition by
username, starttime, logontime, endtime order by username)
FROM tbltime)
delete tmp where rn>1
GOASKER
OK, now first query is working as expected. thanks for that.
And second and third throws an error, but I think, you mean tblTime and not tmp, right? After correcting this, it's working fine.
One last question before using this in my production envirnoment. Will this new column (cid) affects the program that writes the data into the db? The application is using datasets generated by Visual Studio.
And second and third throws an error, but I think, you mean tblTime and not tmp, right? After correcting this, it's working fine.
One last question before using this in my production envirnoment. Will this new column (cid) affects the program that writes the data into the db? The application is using datasets generated by Visual Studio.
>Will this new column (cid) affects the program that writes the data into the db?
only if the code does NOT specify all the columns to be inserted:
INSERT INTO yourtable ( col1, col2 , col3 ... ) VALUES ( .... )
if it does without the column list:
INSERT INTO yourtable VALUES ( .... )
it shall fail...
only if the code does NOT specify all the columns to be inserted:
INSERT INTO yourtable ( col1, col2 , col3 ... ) VALUES ( .... )
if it does without the column list:
INSERT INTO yourtable VALUES ( .... )
it shall fail...
Have to disagree with you there angelIII.
Identity columns are defaulted. See the example here before and after adding the ID column. Still works.
create table testtbl1 (a int, b int)
GO
insert testtbl1 values (1,2)
insert testtbl1 (a,b) values (1,2)
GO
alter table testtbl1 add id int identity
GO
alter table testtbl1 add primary key(id)
GO
insert testtbl1 values (3,4)
insert testtbl1 (a,b) values (5,6)
GO
Identity columns are defaulted. See the example here before and after adding the ID column. Still works.
create table testtbl1 (a int, b int)
GO
insert testtbl1 values (1,2)
insert testtbl1 (a,b) values (1,2)
GO
alter table testtbl1 add id int identity
GO
alter table testtbl1 add primary key(id)
GO
insert testtbl1 values (3,4)
insert testtbl1 (a,b) values (5,6)
GO
>Still works.
until the day another column is added to the table ...
I prefer to tell "i won't work" in this situation, as it's only a non-professional option that it does work ...
just my 3cents
until the day another column is added to the table ...
I prefer to tell "i won't work" in this situation, as it's only a non-professional option that it does work ...
just my 3cents
That's probably a good general advice, but the question was
>> Will this new column (cid) affects the program that writes the data into the db? The application is using datasets generated by Visual Studio.
The answer is no. It will not fail.
>> Will this new column (cid) affects the program that writes the data into the db? The application is using datasets generated by Visual Studio.
The answer is no. It will not fail.
ASKER
Just tried it and it works as expected. The application uses this insert query:
INSERT INTO tblTime
(Computername, StartTime, LogonTime, EndTime, TimeStartLogon, TimeLogonEnd, TimeStartEnd, Username)
VALUES (@Computername,@StartTime,
LAst question before assigning the points to both of you. What does the keyword clustered (third query) mean?
INSERT INTO tblTime
(Computername, StartTime, LogonTime, EndTime, TimeStartLogon, TimeLogonEnd, TimeStartEnd, Username)
VALUES (@Computername,@StartTime,
LAst question before assigning the points to both of you. What does the keyword clustered (third query) mean?
it's requesting the key to be created as clustered index:
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx
>That's probably a good general advice, but the question was
sure. I still prefer to play safe, and prevent newbies to run into the "obvious" pitfalls
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx
>That's probably a good general advice, but the question was
sure. I still prefer to play safe, and prevent newbies to run into the "obvious" pitfalls