check previous row in the table

Hi...

Is there any way to check previous row in SQL Query?

I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes

I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.

Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD

In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.

Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.You can use a Cursor in a Stored Procedure. It would be very complex though...|||Check out the EXISTS keyword (assuming SQL server)...sql

Check integrity using Triggers

I'm trying to check integerity using Delete triggers between 2 database but
from my C# apllication t always say that the query successfull!!
how could i make this work'Hi
Post the DLL and DML and then we can help you.
We can't guess what your T-SQL code and tables look like.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:umCrBob7FHA.3416@.TK2MSFTNGP15.phx.gbl...
> I'm trying to check integerity using Delete triggers between 2 database
> but from my C# apllication t always say that the query successfull!!
> how could i make this work'
>
>

Check If View Exists

I'm working on a project for my class. I have to test for the existance of a certain view, and if it exists I have to delete it before recreating it. I figure it involves an IF command, and I know how to create a view. Everything in between is a blank for me. Any help would be appreciated. Thanks in advance.

An easy way to do this is to use the INFORMATION SCHEMA objects. They provide a handy way to get at the tables/views/etc inside the database..

Code Snippet

USE MyDatabase;

SELECT * FROM INFORMATION_SCHEMA.VIEWS

If you need to look for anything else inside the database, there are a bunch of others:

http://msdn2.microsoft.com/en-us/library/ms186778.aspx

|||I really appreciate that. Thank you.

Check If View Exists

I'm working on a project for my class. I have to test for the existance of a certain view, and if it exists I have to delete it before recreating it. I figure it involves an IF command, and I know how to create a view. Everything in between is a blank for me. Any help would be appreciated. Thanks in advance.

An easy way to do this is to use the INFORMATION SCHEMA objects. They provide a handy way to get at the tables/views/etc inside the database..

Code Snippet

USE MyDatabase;

SELECT * FROM INFORMATION_SCHEMA.VIEWS

If you need to look for anything else inside the database, there are a bunch of others:

http://msdn2.microsoft.com/en-us/library/ms186778.aspx

|||I really appreciate that. Thank you.

Check If View Exists

I'm working on a project for my class. I have to test for the existance of a certain view, and if it exists I have to delete it before recreating it. I figure it involves an IF command, and I know how to create a view. Everything in between is a blank for me. Any help would be appreciated. Thanks in advance.

An easy way to do this is to use the INFORMATION SCHEMA objects. They provide a handy way to get at the tables/views/etc inside the database..

Code Snippet

USE MyDatabase;

SELECT * FROM INFORMATION_SCHEMA.VIEWS

If you need to look for anything else inside the database, there are a bunch of others:

http://msdn2.microsoft.com/en-us/library/ms186778.aspx

|||I really appreciate that. Thank you.

Check if Delete ran in a trigger

I am new to SQL Server and I am trying to write a trigger where I am doing a
delete on another table. I need to know whether this delete fails or not.
How can I achieve this?
TIA
Altmancheck the rowcount in the trigger
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||What do you mean by fail? If the delete fails with an error message, like a
foreign key constraint
violation, the trigger will not be fired. If you mean modify zero rows, chec
k @.@.ROWCOUNT the very
first thing you do in the triggers, or check the number of rows in the delet
ed table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.
gbl...
>I am new to SQL Server and I am trying to write a trigger where I am doing
a delete on another
>table. I need to know whether this delete fails or not. How can I achieve
this?
> --
> TIA
> Altman
>
>|||I mean that I have a trigger and inside of that trigger I am calling a
Delete from table ..... I need to know if the delete that I am calling
inside the trigger goes through.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> What do you mean by fail? If the delete fails with an error message, like
> a foreign key constraint violation, the trigger will not be fired. If you
> mean modify zero rows, check @.@.ROWCOUNT the very first thing you do in the
> triggers, or check the number of rows in the deleted table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Altman" <NotGiven@.SickOfSpam.com> wrote in message
> news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>|||It depends on what you mean by "goes though". If it generates and error, you
can catch that error in
your trigger code (@.@.error), just like you do in any TSQL code. And you can
get the number of rows
modified using @.@.ROWCOUNT. I suggest you check out the error handling articles at
www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:O4jWsSm2FHA.2624@.TK2MSFTNGP09.phx.
gbl...
>I mean that I have a trigger and inside of that trigger I am calling a Dele
te from table ..... I
>need to know if the delete that I am calling inside the trigger goes throug
h.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

Check for constraint on delete

How do you code a procedure to delete a record where if it has an
error (because of foeign key constraint , no cascade and related
records) it will continue and do an update instead.
I have tried this but it doesnt continue if the delete hits an error.
Thanks
Create procedure dbo.delete_record
@.id smallint
as
delete from table
where id=@.id
if @.@.error <>0
update table set deleted=1
where id=@.idwhy don't you update before deleting?

Check Database Operation

Hi,
I want to check that which operation like Insert, Update or Delete is
performed on the table in a trigger written for all the these opertaions.
Is there is any function from which I can know this?
Thanks.
The only way you can tell in a trigger is the presence or absence of rows in
the inserted and deleted table:
1. No rows in either table - nothing was changed
2. Rows in inserted , none in deleted - Insert
3. Rows in deleted , non in inserted - Delete
4. Rows in both - Update
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:uzu3YIgHEHA.548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to check that which operation like Insert, Update or Delete is
> performed on the table in a trigger written for all the these opertaions.
> Is there is any function from which I can know this?
> Thanks.
>

Check Database Operation

Hi,
I want to check that which operation like Insert, Update or Delete is
performed on the table in a trigger written for all the these opertaions.
Is there is any function from which I can know this?
Thanks.The only way you can tell in a trigger is the presence or absence of rows in
the inserted and deleted table:
1. No rows in either table - nothing was changed
2. Rows in inserted , none in deleted - Insert
3. Rows in deleted , non in inserted - Delete
4. Rows in both - Update
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:uzu3YIgHEHA.548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to check that which operation like Insert, Update or Delete is
> performed on the table in a trigger written for all the these opertaions.
> Is there is any function from which I can know this?
> Thanks.
>

Check Database Operation

Hi,
I want to check that which operation like Insert, Update or Delete is
performed on the table in a trigger written for all the these opertaions.
Is there is any function from which I can know this?
Thanks.The only way you can tell in a trigger is the presence or absence of rows in
the inserted and deleted table:
1. No rows in either table - nothing was changed
2. Rows in inserted , none in deleted - Insert
3. Rows in deleted , non in inserted - Delete
4. Rows in both - Update
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:uzu3YIgHEHA.548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to check that which operation like Insert, Update or Delete is
> performed on the table in a trigger written for all the these opertaions.
> Is there is any function from which I can know this?
> Thanks.
>

Check constraint for identity range

Hi,
When I delete subscriptions and publications and disable the replication,
the check constraint for identity range stays in subscriber for tables. Then
I have to manually drop or disable them. Is it an expected behavior or
there's something wrong in my work? How can I have them removed
automatically?
Thanks in advance,
Leila
I reported this problem to Microsoft already. From what I understand this
request was unanticipated by the design team. They expected that when you
replicate to a subscriber, the publisher subscriber relationship is forever.
They did not anticipate customers breaking replication and then using the
subscriber as a standalone database.
I did post a script here sometime ago to remove these constraints.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Leila" <Leilas@.hotpop.com> wrote in message
news:ecnVFp18EHA.808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I delete subscriptions and publications and disable the replication,
> the check constraint for identity range stays in subscriber for tables.
Then
> I have to manually drop or disable them. Is it an expected behavior or
> there's something wrong in my work? How can I have them removed
> automatically?
> Thanks in advance,
> Leila
>
|||Thanks Hilary,
Could you please post a link to your script again.
Thanks again,
Leila
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uXYke518EHA.1392@.tk2msftngp13.phx.gbl...
>I reported this problem to Microsoft already. From what I understand this
> request was unanticipated by the design team. They expected that when you
> replicate to a subscriber, the publisher subscriber relationship is
> forever.
> They did not anticipate customers breaking replication and then using the
> subscriber as a standalone database.
> I did post a script here sometime ago to remove these constraints.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:ecnVFp18EHA.808@.TK2MSFTNGP10.phx.gbl...
> Then
>