delete triggers

sql2k sp3
I assumed this would work as easily as the Insert and Update Triggers I
wrote did:
create trigger DelAuthors
on authors
for delete
as
begin
set nocount on
delete tmp_authors
where tmp_authors.au_id = deleted.au_id
end
Server: Msg 107, Level 16, State 2, Procedure DelAuthors, Line 7
The column prefix 'deleted' does not match with a table name or alias name
used in the query.
Heres the schema for tmp_authors(and Authors):
CREATE TABLE [dbo].[tmp_authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO
Help is appreciated.
TIA, ChrisRChange your DELETE statement to:
DELETE FROM tmp_authors
WHERE EXISTS
(SELECT *
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id)
That's the ANSI Standard update syntax. SQL Server also supports Microsoft
own proprietary version of the DELETE statement using joins and which,
bizarely, requires an extra FROM clause to do what you are attempting.
DELETE
FROM tmp_authors
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id
David Portas
SQL Server MVP
--|||Thanks David.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ZLWdnU8W65uGbv_fRVn-tw@.giganews.com...
> Change your DELETE statement to:
> DELETE FROM tmp_authors
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id)
> That's the ANSI Standard update syntax. SQL Server also supports Microsoft
> own proprietary version of the DELETE statement using joins and which,
> bizarely, requires an extra FROM clause to do what you are attempting.
> DELETE
> FROM tmp_authors
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id
> --
> David Portas
> SQL Server MVP
> --
>

delete triggers

Hi,, How do i delete all triggers in one database? Quick way.
Thanks
I think you could use the new 'SQL Server Management Studio' to highlight as
many triggers you wanted, right click and select delete. I know you can with
Embarcadero's DBArtisan tool.
hth
"mecn" <mecn2002@.yahoo.com> wrote in message
news:O9pIpnDVGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hi,, How do i delete all triggers in one database? Quick way.
> Thanks
>
|||Thanks
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:e0hhli$177l$1@.sxnews1.qg.com...
>I think you could use the new 'SQL Server Management Studio' to highlight
>as many triggers you wanted, right click and select delete. I know you can
>with Embarcadero's DBArtisan tool.
> hth
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:O9pIpnDVGHA.5148@.TK2MSFTNGP12.phx.gbl...
>

Delete trigger in SQL Server 7

Have gone through BOL and Google, but can't find the answer... please help
with a simple Q. I'm trying to create a simple cascade delete trigger in SQL
Server 7 where deleting "parent" records in table X delete corresponding
child records in table Y.

Table X
=========
X_ID
SOME_VAL

Table Y
=========
Y_ID
X_ID
SOME_VAL

When there is no relationship between X.X_ID and Y.X_ID, the following
trigger works fine:

CREATE TRIGGER "temp" ON x
FOR DELETE
AS

delete
from y
where x_id in (select x_id from deleted)

However, when a relationship is created to enforce referential integrity,
the trigger fails, with a "DELETE statement conflicted with COLUMN REFERENCE
constraint" error. I've seen examples where the trigger says (for example)
"AFTER INSERT", where presumably the code is specifically run after the
event that triggers it -- is there a way of forcing the trigger to run
before the delete from table X is executed? I've tried using "BEFORE
DELETE", but no dice :-\

Thanks!

--
Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI triggers
in their white paper:
<http://www.msdn.microsoft.com/libra...ry/en-us/dnsql2
k/html/sql_refintegrity.asp?frame=true>.

The main consideration is that you cannot have declarative referential
integrity in place when you perform cascading actions in triggers in SQL 7,
..

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Aidan Whitehall" <aidanwhitehall@.fairbanks.co.uk> wrote in message
news:btph6b$kri$1@.sparta.btinternet.com...
> Have gone through BOL and Google, but can't find the answer... please help
> with a simple Q. I'm trying to create a simple cascade delete trigger in
SQL
> Server 7 where deleting "parent" records in table X delete corresponding
> child records in table Y.
> Table X
> =========
> X_ID
> SOME_VAL
> Table Y
> =========
> Y_ID
> X_ID
> SOME_VAL
>
> When there is no relationship between X.X_ID and Y.X_ID, the following
> trigger works fine:
> CREATE TRIGGER "temp" ON x
> FOR DELETE
> AS
> delete
> from y
> where x_id in (select x_id from deleted)
> However, when a relationship is created to enforce referential integrity,
> the trigger fails, with a "DELETE statement conflicted with COLUMN
REFERENCE
> constraint" error. I've seen examples where the trigger says (for example)
> "AFTER INSERT", where presumably the code is specifically run after the
> event that triggers it -- is there a way of forcing the trigger to run
> before the delete from table X is executed? I've tried using "BEFORE
> DELETE", but no dice :-\
>
> Thanks!
> --
> Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
> Macromedia ColdFusion Developer
> Fairbanks Environmental Ltd +44 (0)1695 51775|||> Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI
triggers
> in their white paper:
<http://www.msdn.microsoft.com/libra...ry/en-us/dnsql2
> k/html/sql_refintegrity.asp?frame=true>.

K, thanks -- I'll check that out.

> The main consideration is that you cannot have declarative referential
> integrity in place when you perform cascading actions in triggers in SQL
7,

Damn, damn, damn.

Well, thanks for the clarification, anyway.

Regards

--
Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775|||Aidan Whitehall wrote:

>>The main consideration is that you cannot have declarative referential
>>integrity in place when you perform cascading actions in triggers in SQL
> 7,
> Damn, damn, damn.
> Well, thanks for the clarification, anyway.

If I may request it ... would someone please confirm the above statement
about SQL Server. This statement is untrue is other commercial RDBMS
products, such as Oracle and DB2, and I would be surprised if SQL Server
didn't allow this very basic functionality. Is is still true in the
current version?

Thanks.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||> If I may request it ... would someone please confirm the above statement
> about SQL Server. This statement is untrue is other commercial RDBMS
> products, such as Oracle and DB2, and I would be surprised if SQL Server
> didn't allow this very basic functionality. Is is still true in the
> current version?

One can implement cascading actions via DRI or INSTEAD OF triggers in
current version of SQL Server (SQL Server 2000).

Unfortunately, Aidan is using an older version (SQL 7) in which only the
AFTER trigger model is available. Consequently, it is necessary to enforce
referential integrity in triggers instead of DRI in cases when cascading
actions are performed in triggers. Another alternative is to perform
cascading functions via stored procedures.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Daniel Morgan" <damorgan@.x.washington.edu> wrote in message
news:1073846407.832632@.yasure...
> Aidan Whitehall wrote:
> >>The main consideration is that you cannot have declarative referential
> >>integrity in place when you perform cascading actions in triggers in SQL
> > 7,
> > Damn, damn, damn.
> > Well, thanks for the clarification, anyway.
> If I may request it ... would someone please confirm the above statement
> about SQL Server. This statement is untrue is other commercial RDBMS
> products, such as Oracle and DB2, and I would be surprised if SQL Server
> didn't allow this very basic functionality. Is is still true in the
> current version?
> Thanks.
> --
> Daniel Morgan
> http://www.outreach.washington.edu/...oad/oad_crs.asp
> http://www.outreach.washington.edu/...aoa/aoa_crs.asp
> damorgan@.x.washington.edu
> (replace 'x' with a 'u' to reply)|||Dan Guzman wrote:

>>If I may request it ... would someone please confirm the above statement
>>about SQL Server. This statement is untrue is other commercial RDBMS
>>products, such as Oracle and DB2, and I would be surprised if SQL Server
>>didn't allow this very basic functionality. Is is still true in the
>>current version?
>
> One can implement cascading actions via DRI or INSTEAD OF triggers in
> current version of SQL Server (SQL Server 2000).
> Unfortunately, Aidan is using an older version (SQL 7) in which only the
> AFTER trigger model is available. Consequently, it is necessary to enforce
> referential integrity in triggers instead of DRI in cases when cascading
> actions are performed in triggers. Another alternative is to perform
> cascading functions via stored procedures.

Thanks. I was a bit surprised that such basic functionality might not
exist. In Oracle one can always perform cascading deletes in any code
but the referential constraint, itself, will perform the function. For
example:

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

Thanks again for the clarification.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||> Thanks. I was a bit surprised that such basic functionality might not
> exist. In Oracle one can always perform cascading deletes in any code
> but the referential constraint, itself, will perform the function. For
> example:
> ALTER TABLE child
> ADD CONSTRAINT fk_child_parent
> FOREIGN KEY (test)
> REFERENCES parent (test)
> ON DELETE CASCADE;

The same syntax works in SQL 2000. Thank goodness for ANSI standards ;-)

BEGIN TRAN

CREATE TABLE parent(test int NOT NULL PRIMARY KEY)
CREATE TABLE child(test int NOT NULL PRIMARY KEY)

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

insert into parent values(1)
insert into child values(1)

delete from parent
select * from parent --no rows
select * from child --no rows

ROLLBACK

--
Hope this helps.

Dan Guzman
SQL Server MVP|||Dan Guzman wrote:

>>Thanks. I was a bit surprised that such basic functionality might not
>>exist. In Oracle one can always perform cascading deletes in any code
>>but the referential constraint, itself, will perform the function. For
>>example:
>>
>>ALTER TABLE child
>>ADD CONSTRAINT fk_child_parent
>>FOREIGN KEY (test)
>>REFERENCES parent (test)
>>ON DELETE CASCADE;
>
> The same syntax works in SQL 2000. Thank goodness for ANSI standards ;-)
> BEGIN TRAN
> CREATE TABLE parent(test int NOT NULL PRIMARY KEY)
> CREATE TABLE child(test int NOT NULL PRIMARY KEY)
> ALTER TABLE child
> ADD CONSTRAINT fk_child_parent
> FOREIGN KEY (test)
> REFERENCES parent (test)
> ON DELETE CASCADE;
> insert into parent values(1)
> insert into child values(1)
> delete from parent
> select * from parent --no rows
> select * from child --no rows
> ROLLBACK

Excellent. Thanks.

Like I said ... I would have been quite surprised if it didn't exist.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)

delete trigger for multiple row deletes

I'm racking my brain trying to figure this one out and I have a feeling it's
simpler than I'm making it out to be. I need a trigger that will prevent
delete of certain rows but not all. For example, I have a customer table:
CustID INT (primary key, auto inc)
CustName VARCHAR(50)
and an orders table:
OrderID INT (primary key, auto inc)
CustID INT (FK to other table of course)
ItemOrdered VARCHAR(100)
Here would be the data:
Customer table:
CustID CustName
1 Jones Lumber
2 Smith's Cars
3 Helen's Carpet
Orders table:
OrderID CustID ItemOrdered
1 1 2x4's
2 1 Dowel
3 3 Berber
4 3 Shag
Notice that Smith's Cars is not in the orders table at all.
Now I select all the rows in the customer table and try to delete them. I'd
like the trigger to prevent the delete of any customer that has orders but
allow the others to be deleted. So in this case Jones Lumber and Helen's
Carpet would remain and Smith's Cars would be deleted.
The trouble I'm having is that if I set up a cursor in the trigger to
sequence through the customers, how do I rollback just some of the deletes
but not all and put up an appropriate message?
DECLARE
@.iCustID AS INT,
@.sMsg AS VARCHAR(500)
SET NOCOUNT ON
SET @.sMsg = ''
DECLARE curCustomers CURSOR FOR SELECT C.CustID FROM DELETED C
OPEN curCustomers
FETCH NEXT FROM curCustomers INTO @.iCustID
IF @.@.FETCH_STATUS = 0
BEGIN
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM tblOrders WHERE CustID = @.iCustID) > 0
BEGIN
ROLLBACK TRANSACTION
SET @.sMsg = 'Customers with orders cannot be deleted.'
END
FETCH NEXT FROM curCustomers INTO @.iCustID
END
END
IF @.sMsg <> ''
RAISERROR (@.sMsg, 16, 1)
CLOSE curCustomers
DEALLOCATE curCustomers
===
My original trigger looked like this until I realized it was rolling back
the whole thing without even attempting to test for rows that should be
allowed to be deleted:
IF (SELECT COUNT(*) FROM tblOrders WHERE CustID IN (SELECT CustID FROM
DELETED)) > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Customers with orders cannot be deleted.', 16, 1)
END
Thanks,
KeithKeith G Hicks wrote:
> I'm racking my brain trying to figure this one out and I have a feeling it
's
> simpler than I'm making it out to be. I need a trigger that will prevent
> delete of certain rows but not all. For example, I have a customer table:
> CustID INT (primary key, auto inc)
> CustName VARCHAR(50)
> and an orders table:
> OrderID INT (primary key, auto inc)
> CustID INT (FK to other table of course)
> ItemOrdered VARCHAR(100)
> Here would be the data:
> Customer table:
> CustID CustName
> 1 Jones Lumber
> 2 Smith's Cars
> 3 Helen's Carpet
> Orders table:
> OrderID CustID ItemOrdered
> 1 1 2x4's
> 2 1 Dowel
> 3 3 Berber
> 4 3 Shag
> Notice that Smith's Cars is not in the orders table at all.
> Now I select all the rows in the customer table and try to delete them. I'
d
> like the trigger to prevent the delete of any customer that has orders but
> allow the others to be deleted. So in this case Jones Lumber and Helen's
> Carpet would remain and Smith's Cars would be deleted.
> The trouble I'm having is that if I set up a cursor in the trigger to
> sequence through the customers, how do I rollback just some of the deletes
> but not all and put up an appropriate message?
> DECLARE
> @.iCustID AS INT,
> @.sMsg AS VARCHAR(500)
> SET NOCOUNT ON
> SET @.sMsg = ''
> DECLARE curCustomers CURSOR FOR SELECT C.CustID FROM DELETED C
> OPEN curCustomers
> FETCH NEXT FROM curCustomers INTO @.iCustID
> IF @.@.FETCH_STATUS = 0
> BEGIN
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(*) FROM tblOrders WHERE CustID = @.iCustID) > 0
> BEGIN
> ROLLBACK TRANSACTION
> SET @.sMsg = 'Customers with orders cannot be deleted.'
> END
> FETCH NEXT FROM curCustomers INTO @.iCustID
> END
> END
> IF @.sMsg <> ''
> RAISERROR (@.sMsg, 16, 1)
> CLOSE curCustomers
> DEALLOCATE curCustomers
>
> ===
> My original trigger looked like this until I realized it was rolling back
> the whole thing without even attempting to test for rows that should be
> allowed to be deleted:
> IF (SELECT COUNT(*) FROM tblOrders WHERE CustID IN (SELECT CustID FROM
> DELETED)) > 0
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR ('Customers with orders cannot be deleted.', 16, 1)
> END
> Thanks,
> Keith
Do not use cursors in triggers (or anywhere else you don't have to for
that matter).
I've included a solution below but I don't really see the point. Why
not just modify your original DELETE statement to exclude the
referenced rows using the NOT EXISTS check? The foreign key keeps you
safe from accidental deletes.
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ok so just so I understand your strategy, a rollback is not necessary since
you doing INSETAD OF and are putting up a warning if there are any rows that
cannot be deleted and then actually performing the delete on the allowed
rows within the trigger?
The foreign key won't help me here. The actual project is slightly more
complex but my sample got the point across and your solution should do the
trick nicely. Very much appreciated.
What's the reason for avoiding cursors? Is it a performance issue or are
there other reasons?
Thanks again,
Keith
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1140822038.662268.199890@.e56g2000cwe.googlegroups.com...
Do not use cursors in triggers (or anywhere else you don't have to for
that matter).
I've included a solution below but I don't really see the point. Why
not just modify your original DELETE statement to exclude the
referenced rows using the NOT EXISTS check? The foreign key keeps you
safe from accidental deletes.
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I implemented your suggestion and it works fine if I swap the sections so
that the test for Raiserror is happening after the delete is actually done.
Otherwise nothing deletes for me if it's done in the order you have below.
Can you explain why?
Thanks,
Keith
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Never mind on this question. It works fine either way.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e6xNt2ZOGHA.2604@.TK2MSFTNGP09.phx.gbl...
I implemented your suggestion and it works fine if I swap the sections so
that the test for Raiserror is happening after the delete is actually done.
Otherwise nothing deletes for me if it's done in the order you have below.
Can you explain why?
Thanks,
Keith|||There are several reasons to avoid cursors. Set-based operations generally
perform better: heap and index maintenance can be optimized, and transaction
logging is minimized. In addition, triggers fire only once for a set-based
operation, whereas they fire once per iteration for a row-based operation.
But these aren't the only reasons. Cursors reduce the effective isolation
level to REPEATABLE READ if a transaction is outstanding or to READ
COMMITTED if one isn't. Also, if a transaction is outstanding and
modifications occur within the fetch loop, the probability of deadlocks
increases significantly. You should know that a transaction is always
outstanding within the body of a trigger. While there are rare occasions
where a cursor will improve performance, all of the above factors must be
taken into account before converting a set-based solution into a row-based
one. An important note: you should always attempt a set-based solution
first, and only after every other option has failed, such as adding indexes,
hints, and ultimately SET FORCEPLAN, should you convert a set-based solution
to a row-based one. As you can see, cursors are not for the uninitiated.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23uVtziZOGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Ok so just so I understand your strategy, a rollback is not necessary
> since
> you doing INSETAD OF and are putting up a warning if there are any rows
> that
> cannot be deleted and then actually performing the delete on the allowed
> rows within the trigger?
> The foreign key won't help me here. The actual project is slightly more
> complex but my sample got the point across and your solution should do the
> trick nicely. Very much appreciated.
> What's the reason for avoiding cursors? Is it a performance issue or are
> there other reasons?
> Thanks again,
> Keith
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1140822038.662268.199890@.e56g2000cwe.googlegroups.com...
>
> Do not use cursors in triggers (or anywhere else you don't have to for
> that matter).
> I've included a solution below but I don't really see the point. Why
> not just modify your original DELETE statement to exclude the
> referenced rows using the NOT EXISTS check? The foreign key keeps you
> safe from accidental deletes.
> CREATE TRIGGER trg_customer
> ON customer INSTEAD OF DELETE
> AS
> IF EXISTS
> (SELECT *
> FROM deleted
> WHERE EXISTS
> (SELECT *
> FROM orders
> WHERE custid = deleted.custid))
> RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
> DELETE FROM customer
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE custid = customer.custid)
> AND NOT EXISTS
> (SELECT *
> FROM orders
> WHERE custid = customer.custid);
> GO
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks for all the info Brian.

Delete trigger capturing text data

I am trying to use a trigger to capture text data that is being deleted and
put it into an audittrail table. I use the same logic for the insert
trigger(using inserted instead of deleted) and it functions as it should,
however when I do a delete, this trigger seems to be overlooked as no insert
ever occurs.
create trigger evidence_audit_delete on evidence
for delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowguid
= d.rowguid
endHi tracey,
If I understood it right, rowguid should be the key, so that row would be
deleted when you called the trigger. So the join returns no rows.
rewrite it this way.
create trigger evidence_audit_delete on evidence
for delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(d.note as
varchar(4000)),system_user,@.@.spid from deleted d
end
Let me know if this helps|||I put the join in because you cant seem to use the text data(even if its
converted into a varchar) from the deleted table. Is there a way to get the
trigger to fire before the delete maybe?
Server: Msg 311, Level 16, State 1, Procedure evidence_audit_delete, Line 7
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
tables.
"Omnibuzz" wrote:

> Hi tracey,
> If I understood it right, rowguid should be the key, so that row would be
> deleted when you called the trigger. So the join returns no rows.
> rewrite it this way.
> create trigger evidence_audit_delete on evidence
> for delete
> not for replication
> as
> begin
> insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name
,
> curr_val, username, session_id)
> select getdate(),'DELETE','evidence',d.rowguid,'note',cast(d.note as
> varchar(4000)),system_user,@.@.spid from deleted d
> end
> Let me know if this helps|||try this then (untested)
create trigger evidence_audit_delete on evidence
instead of delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowguid
= d.rowguid
delete from a from evidence a, deleted d where a.rowguid = d.rowguid
end|||That works
"Omnibuzz" wrote:

> try this then (untested)
>
> create trigger evidence_audit_delete on evidence
> instead of delete
> not for replication
> as
> begin
> insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name
,
> curr_val, username, session_id)
> select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
> varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowgu
id
> = d.rowguid
> delete from a from evidence a, deleted d where a.rowguid = d.rowguid
> end|||good. If you are looking no further can you close the thread.
Thanks
Omnibuzz
"Tracey" wrote:
> That works
> "Omnibuzz" wrote:
>

Delete trigger & COM+

Hi, I'm building a website that interacts with MS SQLserver(v7) via a Buisiness Rule-layer developed in COM+ with VB(v6 sp5). The website is an add-on on a existing client/server app. Now I have delete triggers on almost every table and they work perfectly in the c/s app and when runned in the query analyzer. But when I try to do the same with COM+ I get the the next error message on line 10:
"Cannot use SAVE TRANSACTION within a distributed transaction."

Here follows the code. Hopefully anybody can help me with this problem.

SQL-statement:

Function getDeleteRequestSQL(ByRef strRequestId As String) As String
Dim strSQL As String
strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'"
getDeleteRequestSQL = strSQL
End Function

And then the place where the error occurs.

GetConnection cnConn
strSQL = getDeleteRequestSQL(reqId)
10 cnConn.Execute strSQL, , adExecuteNoRecords

And finaly the trigger:

create trigger td_thmld2 on thmld2 for delete as
begin
declare
@.numrows int,
@.errno int,
@.errmsg varchar(255)

select @.numrows = @.@.rowcount
if @.numrows = 0
return

select @.numrows = (select count(*) from trigstat with (nolock) )
if @.numrows > 0
return

save transaction trans_td_thmld2 <-- REASON FOR ERROR?

/* delete all children in "thmstat" */
delete thmstat
from thmstat t2, deleted t1
where t2.hdmcode = t1.hdmcode

/* delete all children in "thmldlk1" */
delete thmldlk1
from thmldlk1 t2, deleted t1
where t2.hdmlmldcode = t1.hdmcode

/* errors handling */
error:
raiserror @.errno @.errmsg
rollback transaction trans_td_thmld2
endDon't know what that is, but it has nothing to do with the trigger...

Well that me guessing again...

Sounds more like you're establishing a tran across many servers...|||The components I've created are all located within one dll. The dll is active in the component services on my own webserver and is working fine. So the action isn't distributed over the network. :(

Delete Trigger

Dear All
I am trying to create a DELETE Trigger on a table which will delete a
previous record for a given value entered.
[1] I create a table with two columns called ItemName, TimeStamp (when the
record was last inserted/updated).
[2] I Insert a value of 'Table' into ItemName.
[3] I Insert a value of 'Chair' into ItemName
[4] If I add another entry of 'Table' into ItemName and the Trigger will
find the previous entry for 'Table' and delete it.
What I don't understand is how to get the value on the newly added column
and then serach for the 'duplicate' record to delete?
I apologise if I am not clear as I am a newbie at SQL Server.
Thanks for the continued support of the group and especially Louis Davidson
who helped me with a similar post.
Alastair MacFarlaneyou dont need a triger for delete, you need a trigger for insert that
deletes the record. In the trigger, you have a table 'inserted'. Table
'inserted' has new data, or data you're inserting. So you can write
something like:
CREATE TRIGGER trigerName
ON Table
FOR INSERT
AS
BEGIN
delete
from
table t
inner join inserted i on t.itemname = i.itemname
where
t.timestamp < i.timestamp
END
This will delete ALL records from table with the same itemName and lower
timestamp. You also might want to think about updates.
What should happen if someone updates the ItemName column?
MC
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:8306D794-88DB-4C39-942D-BF62E7822170@.microsoft.com...
> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis
> Davidson
> who helped me with a similar post.
> Alastair MacFarlane|||Hi
I think you need a trigger for INSERT
Try this one (untested)
create table r (col1 int not null primary key,
col2 char(1) not null)
create trigger my_tr on r for insert
as
if exists (select count(*),col2 from
r group by col2 having count(*)>1)
delete r
from r join inserted i on r.col2=i.col2 and r.col1<i.col1
--test
insert into r values (1,'a')
insert into r values (2,'b')
insert into r values (3,'c')
insert into r values (4,'c')
select * from r
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:8306D794-88DB-4C39-942D-BF62E7822170@.microsoft.com...
> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis
> Davidson
> who helped me with a similar post.
> Alastair MacFarlane|||An instead of trigger would do well for this
CREATE TABLE furniture (ItemName varchar(512), [TimeStamp] timestamp)
GO
INSERT INTO furniture (ItemName) VALUES ('TABLE')
INSERT INTO furniture (ItemName) VALUES ('CHAIR')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
---
TABLE 0x0000000000002025
CHAIR 0x0000000000002026
*/
DROP TRIGGER trigger1
GO
CREATE TRIGGER trigger1 ON furniture
INSTEAD OF INSERT
AS
SET NOCOUNT ON
UPDATE furniture SET ItemName = i.ItemName
FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
INSERT INTO furniture (ItemName) SELECT ItemName FROM inserted i
WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemName)
SET NOCOUNT OFF
INSERT INTO furniture (ItemName) VALUES ('Ottoman')
INSERT INTO furniture (ItemName) VALUES ('TABLE')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 0x0000000000002029
CHAIR 0x0000000000002026
Ottoman 0x0000000000002028
*/
--
"Alastair MacFarlane" wrote:

> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis Davidso
n
> who helped me with a similar post.
> Alastair MacFarlane|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:583593
Alastair,
I'm not sure you really want to create a duplicate and delete it. Here
is another way (Uri, thanks for setting up the data):
create table r (col1 int not null primary key,
col2 char(1) not null)
go
create view r_v
as
select * from r
go
create trigger my_tr on r_v
instead of insert
as
begin
update r set r.col2 = inserted.col2
from r,inserted
where r.col1=inserted.col1
insert into r
select inserted.col1, inserted.col2
from inserted left outer join r
on r.col1=inserted.col1
where r.col1 is null
end
go
--test
insert into r values (1,'a')
insert into r values (2,'b')
insert into r values (3,'c')
-- this will update the row with col1=1
insert into r_v values (1,'c')
go
select * from r
col1 col2
-- --
1 c
2 b
3 c
go
insert into r_v
-- this row is inserted
select 4, 'D'
union all
-- this row is updated
select 2, 'e'
go
select * from r
col1 col2
-- --
1 c
2 e
3 c
4 D
(4 row(s) affected)
go
drop table r
drop view r_v|||Sorry, I thought from your DDL that you were using a column with a datatype
of timestamp. Here is the trigger re-written for a column of datatype
datetime.
DROP TABLE furniture
GO
CREATE TABLE furniture (ItemName char(20), [TimeStamp] datetime)
GO
INSERT INTO furniture VALUES ('TABLE',GETDATE())
INSERT INTO furniture VALUES ('CHAIR',GETDATE())
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 2006-02-09 08:38:35.173
CHAIR 2006-02-09 08:38:35.190
*/
DROP TRIGGER trigger1
GO
CREATE TRIGGER trigger1 ON furniture
INSTEAD OF INSERT
AS
SET NOCOUNT ON
UPDATE furniture SET [TimeStamp] = GETDATE()
FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
INSERT INTO furniture SELECT ItemName, GETDATE() FROM inserted i
WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemName)
SET NOCOUNT OFF
INSERT INTO furniture (ItemName) VALUES ('Ottoman')
INSERT INTO furniture (ItemName) VALUES ('TABLE')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 2006-02-09 08:39:45.410
CHAIR 2006-02-09 08:38:35.190
Ottoman 2006-02-09 08:39:43.440
*/
"Mark Williams" wrote:
> An instead of trigger would do well for this
> CREATE TABLE furniture (ItemName varchar(512), [TimeStamp] timestamp)
> GO
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> INSERT INTO furniture (ItemName) VALUES ('CHAIR')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> ---
> TABLE 0x0000000000002025
> CHAIR 0x0000000000002026
> */
> DROP TRIGGER trigger1
> GO
> CREATE TRIGGER trigger1 ON furniture
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> UPDATE furniture SET ItemName = i.ItemName
> FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
> INSERT INTO furniture (ItemName) SELECT ItemName FROM inserted i
> WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemNam
e)
> SET NOCOUNT OFF
> INSERT INTO furniture (ItemName) VALUES ('Ottoman')
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 0x0000000000002029
> CHAIR 0x0000000000002026
> Ottoman 0x0000000000002028
> */
> --
> "Alastair MacFarlane" wrote:
>|||Mark and others,
Sorry for the delay and thanks for the feedback. I have tested out the
examples provided and they work the way I would expect.
Your help is appreciated.
Alastair MacFarlane
"Mark Williams" wrote:
> Sorry, I thought from your DDL that you were using a column with a datatyp
e
> of timestamp. Here is the trigger re-written for a column of datatype
> datetime.
> DROP TABLE furniture
> GO
> CREATE TABLE furniture (ItemName char(20), [TimeStamp] datetime)
> GO
> INSERT INTO furniture VALUES ('TABLE',GETDATE())
> INSERT INTO furniture VALUES ('CHAIR',GETDATE())
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 2006-02-09 08:38:35.173
> CHAIR 2006-02-09 08:38:35.190
> */
> DROP TRIGGER trigger1
> GO
> CREATE TRIGGER trigger1 ON furniture
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> UPDATE furniture SET [TimeStamp] = GETDATE()
> FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
> INSERT INTO furniture SELECT ItemName, GETDATE() FROM inserted i
> WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemNam
e)
> SET NOCOUNT OFF
> INSERT INTO furniture (ItemName) VALUES ('Ottoman')
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 2006-02-09 08:39:45.410
> CHAIR 2006-02-09 08:38:35.190
> Ottoman 2006-02-09 08:39:43.440
> */
> --
> "Mark Williams" wrote:
>

delete trigger

Hi how can i use delete trigger?

For example i've a two table like emp and emp_personal and now what i want to do is

i want to delete one row from emp table so how is it possible to delete automatically that emp's details from second table(emp_personal) ?

Ex:

emp emp_personal

emp_id emp_name emp_basic emp_id emp_address

101 Nagu 32,000 101 India

102 Vijay 35,000 102 South Africa

103 Ritesh 30,000 103 U.S

I want to delete employee who hav emp_id of 102 from emp table , so how can i delete automatically that employee details from second table i.e. emp_personal ?

Is it possible with triggers?

Thanx - Nagu

hi Nagu,

you can implement a trigger on DELETE action but you can even implement a foreign key constraint with the desired action, that's to say ON UPDATE CASCADE and ON DELETE CASCADE to maintain referential integrity as

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.Emp (

emp_id int NOT NULL PRIMARY KEY,

emp_name varchar(10) NOT NULL,

emp_basic decimal (18,4) NULL

);

CREATE TABLE dbo.Emp_Personal (

emp_id int NOT NULL PRIMARY KEY,

emp_address varchar(15) NOT NULL

);

GO

INSERT INTO dbo.Emp VALUES ( 101 , 'Nagu', 32 );

INSERT INTO dbo.Emp VALUES ( 102 , 'Vijay', 35 );

INSERT INTO dbo.Emp VALUES ( 103 , 'Rithesh', 30 );

INSERT INTO dbo.Emp_Personal VALUES ( 101, 'India' );

INSERT INTO dbo.Emp_Personal VALUES ( 102, 'South Africa' );

INSERT INTO dbo.Emp_Personal VALUES ( 103, 'US' );

GO

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

CREATE TRIGGER tr_D_Emp ON dbo.Emp

FOR DELETE

AS BEGIN

IF @.@.ROWCOUNT = 0 RETURN;

DELETE FROM dbo.Emp_Personal

WHERE emp_id IN (SELECT emp_id FROM deleted);

END;

GO

DELETE FROM dbo.Emp WHERE emp_id > 101;

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

DROP TRIGGER tr_D_Emp ;

GO

ALTER TABLE dbo.Emp_Personal

ADD CONSTRAINT fk_Emp$have$Emp_Personal

FOREIGN KEY (emp_id)

REFERENCES dbo.Emp (emp_id)

ON UPDATE CASCADE

ON DELETE CASCADE;

GO

INSERT INTO dbo.Emp VALUES ( 102 , 'Vijay', 35 );

INSERT INTO dbo.Emp VALUES ( 103 , 'Rithesh', 30 );

INSERT INTO dbo.Emp_Personal VALUES ( 102, 'South Africa' );

INSERT INTO dbo.Emp_Personal VALUES ( 103, 'US' );

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

DELETE FROM dbo.Emp WHERE emp_id < 103;

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

DROP TABLE dbo.Emp_Personal, dbo.Emp;

--<--

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

102 Vijay 35.0000 South Africa

103 Rithesh 30.0000 US

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

102 Vijay 35.0000 South Africa

103 Rithesh 30.0000 US

emp_id emp_name emp_basic emp_address

-- -

103 Rithesh 30.0000 US

regards

|||

Thank you Andrea

Nagu

DELETE Trigger

Hi,
I am pretty sure this is fairly straightforward, I would like to use a
trigger to delete an existing record from a table where the value of certain
fields match those of the record to be inserted.
Thanks.Hi Redowl,
CREATE TRIGGER SomeDelTrigger ON SomeTable
FOR INSERT
AS
BEGIN
DELETE
FROM SomeOtherTable
INNER JOIN INSERTED
ON SomeOtherTable.COl01 = INSERTED.Col01
-- (...) other columns to join on
END
HTH, Jens Suessmeyer|||Without knowing the details of your underlying tables
CREATE TRIGGER deleteRows ON [YourTable]
AFTER INSERT
AS
DELETE FROM [YourTable] WHERE [YourTable].col1=inserted.col1
AND [YourTable].col2=inserted.col2 --check to match more if necessary
Keep in mind that an AFTER trigger will only execute if the statement that
caused the trigger to fire would have normally succeeded. If you are trying
to delete a row that would have caused a violation of a constraint, use a
INSTEAD OF trigger.
--
"Redowl" wrote:

> Hi,
> I am pretty sure this is fairly straightforward, I would like to use a
> trigger to delete an existing record from a table where the value of certa
in
> fields match those of the record to be inserted.
> Thanks.
>
>|||CREATE TRIGGER SomeDelTrigger ON SomeTable
FOR INSERT
AS
BEGIN
DELETE
FROM SomeotherTable
INNER JOIN INSERTED
ON SomeotherTable.Col1 = INSERTED.Col1
--(...) and so on, name the joined columns
END
HTH, jens Suessmeyer.|||Thanks everybody for the responses
Mark, will your suggestion not delete the newly added row as well ?
"Mark Williams" wrote:
> Without knowing the details of your underlying tables
> CREATE TRIGGER deleteRows ON [YourTable]
> AFTER INSERT
> AS
> DELETE FROM [YourTable] WHERE [YourTable].col1=inserted.col1
> AND [YourTable].col2=inserted.col2 --check to match more if necessary
>
> Keep in mind that an AFTER trigger will only execute if the statement that
> caused the trigger to fire would have normally succeeded. If you are tryin
g
> to delete a row that would have caused a violation of a constraint, use a
> INSTEAD OF trigger.
> --
>
> "Redowl" wrote:
>|||Jens,
Thanks for the response. I want to delete an existing row from the same
table I am inserting into.
I want to delete any existing rows that match a certain criteria. e.g A new
row could have surname = 'Jones' and firstname = 'Bob' and age='30'. I want
to check if there is already a row with surname = 'Jones' and firstname =
'Bob' and delete this?
Grateful for any help.
"Jens" wrote:

> CREATE TRIGGER SomeDelTrigger ON SomeTable
> FOR INSERT
> AS
> BEGIN
> DELETE
> FROM SomeotherTable
> INNER JOIN INSERTED
> ON SomeotherTable.Col1 = INSERTED.Col1
> --(...) and so on, name the joined columns
> END
>
> HTH, jens Suessmeyer.
>

delete trigger

I am trying to create a trigger where if the check box iin infopath is
checked then it deletes the whole record from the database. I am not sure
where to even start on this trigger. Please Help.Hi Ben
You may get a better response on how infopath works in
microsoft.public.infopath
John
"Ben Watts" wrote:
> I am trying to create a trigger where if the check box iin infopath is
> checked then it deletes the whole record from the database. I am not sure
> where to even start on this trigger. Please Help.
>
>

Delete Trigger

I'm trying to get a record added to clsrecord_accessed
when deleting (as an example) notes from the LONGNOTES
field. This isn't appearing to happen though with the
below code. What am I missing?
CREATE TRIGGER Prescott_Delete_ACTIVITY
ON sysdba.ACTIVITY
FOR DELETE
AS
IF UPDATE (LONGNOTES)
or UPDATE (DESCRIPTION)
or UPDATE (STARTDATE)
BEGIN
IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
= 'ABENJ0000022')
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Delete' FROM
Deleted D WHERE AccountID = 'ABENJ0000022'
ENDCheck out BOL for "create trigger". IF UPDATE() is not useful in a delete
trigger since columns are "mentioned" in the delete statement.
"CalTab" <anonymous@.discussions.microsoft.com> wrote in message
news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
> = 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
> END|||> columns are "mentioned" in the delete statement.
Did you mean "not mentioned"?|||so I change it to this, and still nothing:
CREATE TRIGGER Prescott_Update_ACTIVITY
ON sysdba.ACTIVITY
FOR Insert, Update, Delete
AS
IF NOT EXISTS (SELECT * FROM Inserted I WHERE I.AccountID
= 'ABENJ0000022'
IF EXISTS (SELECT * FROM Deleted I WHERE I.AccountID
= 'ABENJ0000022'
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Inserted I on CA.CLSRECORD_ACCESSEDID = I.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE(), I.CONTACTID,
I.ACCOUNTID, 'Activity Change' FROM
Inserted I
ELSE
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Deleted' FROM
Deleted D
GO
>--Original Message--
>Check out BOL for "create trigger". IF UPDATE() is not
useful in a delete
>trigger since columns are "mentioned" in the delete
statement.
>"CalTab" <anonymous@.discussions.microsoft.com> wrote in
message
>news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
>> I'm trying to get a record added to clsrecord_accessed
>> when deleting (as an example) notes from the LONGNOTES
>> field. This isn't appearing to happen though with the
>> below code. What am I missing?
>> CREATE TRIGGER Prescott_Delete_ACTIVITY
>> ON sysdba.ACTIVITY
>> FOR DELETE
>> AS
>> IF UPDATE (LONGNOTES)
>> or UPDATE (DESCRIPTION)
>> or UPDATE (STARTDATE)
>> BEGIN
>> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>> = 'ABENJ0000022')
>> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
>> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
>> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
>> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID,
RECORD_TYPE)
>> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(),
D.CONTACTID,
>> D.ACCOUNTID, 'Activity Delete' FROM
>> Deleted D WHERE AccountID = 'ABENJ0000022'
>> END
>
>.
>|||CalTab (anonymous@.discussions.microsoft.com) writes:
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>= 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
I took the liberty to reformat your trigger body, to make it more
readable:
1) IF NOT EXISTS (SELECT * FROM Inserted I
WHERE I.AccountID = 'ABENJ0000022')
BEGIN
2) IF EXISTS (SELECT * FROM Deleted I
WHERE I.AccountID = 'ABENJ0000022')
BEGIN
3) IF NOT EXISTS (SELECT *
FROM CLSRECORD_ACCESSED CA
JOIN Inserted I
on CA.CLSRECORD_ACCESSEDID = I.ACTIVITYID)
BEGIN
A) INSERT CLSRECORD_ACCESSED(
CLSRECORD_ACCESSEDID, REATEUSER, CREATEDATE, CONTACTID,
ACCOUNTID, RECORD_TYPE)
SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE(), I.CONTACTID,
I.ACCOUNTID, 'Activity Change'
FROM Inserted I
END
ELSE IF NOT EXISTS (SELECT *
FROM CLSRECORD_ACCESSED CA
JOIN Deleted D
on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
BEGIN
B) INSERT CLSRECORD_ACCESSED(
CLSRECORD_ACCESSEDID, CREATEUSER, CREATEDATE, CONTACTID,
ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Deleted'
FROM Deleted D
END
END
END
So if this code is trigger by a DELETE, we will pass the first IF
statement, because Inserted is empty in a DELETE trigger. If the account
is the right one, we will pass the second IF statement too. And we
will always pass the third IF statement, because Inserted is empty.
This means that we will execute INSERT statement A, but since Inserted
is empty, nothing will happen.
The test in the ELSE IF statement is never executed, and neither is
INSERT statement B, when trigger is fired by DELETE.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uh... why do you have hard-coded conditionals in your trigger? Wouldn't it
make more sense to put these values somewhere instead of hard-coding values?
Why are you using the inserted table (it is not populated by a DELETE)?
"CalTab" <anonymous@.discussions.microsoft.com> wrote in message
news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
> = 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
> END|||so, is this code useable?
>--Original Message--
>CalTab (anonymous@.discussions.microsoft.com) writes:
>> I'm trying to get a record added to clsrecord_accessed
>> when deleting (as an example) notes from the LONGNOTES
>> field. This isn't appearing to happen though with the
>> below code. What am I missing?
>> CREATE TRIGGER Prescott_Delete_ACTIVITY
>> ON sysdba.ACTIVITY
>> FOR DELETE
>> AS
>> IF UPDATE (LONGNOTES)
>> or UPDATE (DESCRIPTION)
>> or UPDATE (STARTDATE)
>> BEGIN
>> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>>= 'ABENJ0000022')
>> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA
JOIN
>> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
>> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
>> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID,
RECORD_TYPE)
>> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(),
D.CONTACTID,
>> D.ACCOUNTID, 'Activity Delete' FROM
>> Deleted D WHERE AccountID = 'ABENJ0000022'
>I took the liberty to reformat your trigger body, to
make it more
>readable:
>1) IF NOT EXISTS (SELECT * FROM Inserted I
> WHERE I.AccountID = 'ABENJ0000022')
> BEGIN
>2) IF EXISTS (SELECT * FROM Deleted I
> WHERE I.AccountID = 'ABENJ0000022')
> BEGIN
>3) IF NOT EXISTS (SELECT *
> FROM CLSRECORD_ACCESSED CA
> JOIN Inserted I
> on CA.CLSRECORD_ACCESSEDID =I.ACTIVITYID)
> BEGIN
>A) INSERT CLSRECORD_ACCESSED(
> CLSRECORD_ACCESSEDID, REATEUSER,
CREATEDATE, CONTACTID,
> ACCOUNTID, RECORD_TYPE)
> SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE
(), I.CONTACTID,
> I.ACCOUNTID, 'Activity Change'
> FROM Inserted I
> END
> ELSE IF NOT EXISTS (SELECT *
> FROM CLSRECORD_ACCESSED
CA
> JOIN Deleted D
> on
CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> BEGIN
>B) INSERT CLSRECORD_ACCESSED(
> CLSRECORD_ACCESSEDID, CREATEUSER,
CREATEDATE, CONTACTID,
> ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE
(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Deleted'
> FROM Deleted D
> END
> END
> END
>
>So if this code is trigger by a DELETE, we will pass the
first IF
>statement, because Inserted is empty in a DELETE
trigger. If the account
>is the right one, we will pass the second IF statement
too. And we
>will always pass the third IF statement, because
Inserted is empty.
>This means that we will execute INSERT statement A, but
since Inserted
>is empty, nothing will happen.
>The test in the ELSE IF statement is never executed, and
neither is
>INSERT statement B, when trigger is fired by DELETE.
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/boo
ks.asp
>.
>|||AnonymousWheel (anonymous@.discussions.microsoft.com) writes:
> so, is this code useable?
I don't if you are the person who asked the original question, or someone
else.
Obviously, the trigger code in its current form does not seem to be
extremely useful, since it does not perform the intended task.
But I can't tell how from the mark it is, since I don't know the
business requirements.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Oops - yes, exactly.
"Foo Man Chew" <foo@.man.chew> wrote in message
news:uFsfwZX0DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > columns are "mentioned" in the delete statement.
> Did you mean "not mentioned"?
>|||all we're trying to do is whenever there is an
Insert,update or delete in the activity table, we'd like a
row inserted in clsrecord_accessed to produce an "audit"
of the activity.
>--Original Message--
>AnonymousWheel (anonymous@.discussions.microsoft.com)
writes:
>> so, is this code useable?
>I don't if you are the person who asked the original
question, or someone
>else.
>Obviously, the trigger code in its current form does not
seem to be
>extremely useful, since it does not perform the intended
task.
>But I can't tell how from the mark it is, since I don't
know the
>business requirements.
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>.
>

delete trigger

I am trying to create a trigger where if the check box iin infopath is
checked then it deletes the whole record from the database. I am not sure
where to even start on this trigger. Please Help.Hi Ben
You may get a better response on how infopath works in
microsoft.public.infopath
John
"Ben Watts" wrote:

> I am trying to create a trigger where if the check box iin infopath is
> checked then it deletes the whole record from the database. I am not sure
> where to even start on this trigger. Please Help.
>
>

DELETE transaction with SNAPSHOT isolation level - conflicts another table

Hi,

we are executing the following query in a stored procedure using snapshot isolation level:

DELETE FROM tBackgroundProcessProgressReport
FROM tBackgroundProcessProgressReport LEFT OUTER JOIN
tBackgroundProcess ON
tBackgroundProcess.BackgroundProcessProgressReportID =tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFTOUTER JOIN
tBackgroundProcessProgressReportItem ON
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID =tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportID
WHERE (tBackgroundProcess.BackgroundProcessID IS NULL) AND
(tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)

The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.
However, for some reasone we get the following exception:

System.Data.SqlClient.SqlException:Snapshot isolation transaction aborted due to update conflict. Youcannot use snapshot isolation to access table 'dbo.tBackgroundProcess'directly or indirectly in database 'RHSS_PRD_PT_Engine' to update,delete, or insert the row that has been modified or deleted by anothertransaction. Retry the transaction or change the isolation level forthe update/delete statement.

The exception specifies that we arenot allowed to update/delete/insert records in tBackgroundProcess, butthe query indeed deletes records from tBackgroundProcessProgressReport,not from the table in the exception.
Is the exception raised because of the join?

Has someone encountered this issue before?

Thanks,

Yani

Hi,

it looks like this forum is not the best place to ask, since it's dedicated to asp.net

So anybody with idea where i could ask for a solution for my problem?

Thanks in advance!

Delete Transaction Log

Hi all,

I am using MSSQL Server 2000. I have my SQL Server Database: mydb.mdf (1 GB) and mydb_log.ldf (30 GB).
Now I would like to delete this VERY BIG transaction log and let the SQL Server automatic create a new one. What is the best way to do that? System downtime is available. Here is my way when I tested on development machine:
1) Backup full database and transaction log.
2) Detach DB by using Enterprise Manager
3) Delete transaction log (mydb_log.ldf file) by using Windows Explorer.
4) Attach DB.
Then I saw SQL Server automatic created a new Transaction log (mydb_log.ldf) only 1 MB in size.

Before I apply on Production, I would like to ask for your idea? Any warning? If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
I appreciate for all help.
ThanksHave you tried dbcc shrinkfile / dbcc shrinkdatabase ? Have a look at the following article:

article (http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318)

If all else fails, then your solution is the quickest.

Good luck.|||On deleting Transaction logs:

Re: Before I apply on Production, I would like to ask for your idea? Any warning? If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
I appreciate for all help

Q1 Before I apply on Production, I would like to ask for your idea?
A1 Why not use DBCC ShrinkFile, and / or enable autoshrink? Also, why is your Log so much larger than your Data e.g., (i Are you not frequently dumping / backing up your Log to transaction log backup *.trn files? ; ii Or, do you have long running transactions that are filling up your DB Log; iii Or, is your DB very heavily used, etc.?)?

DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk

Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go

Q2 Any warning?
A2 You may "get away with" using your method indefinitly; however it is not safe.

Q3 If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
A3 BOL = Books On Line. You may install BOL using the Sql Server installer, it covers various recommended DBMS methods for reducing / limiting transaction log file sizes e.g., (including DBCC ShrinkFile, DBCC ShrinkDatabase). Microsoft Technet is another source of documentation that may help if / when your method gets you into trouble.|||Many thanks for all your help.
First of all, I just received new role as a DBA. Then I found my DB is not monitored, dumped/backed up to *TRN for a long time.
Why I do not want to use SHRINKFile? It seems to me DBCC SHRINKFILE does not work well, the physical size not reduce much as I expected! So I would like to "delete" and create a new log file.

Regards,
John|||Bill,
Below is a reply I made to a post from a month or so ago. Think it will help you out.

A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

Hope this helps.

Brent|||JohnBill,
Below is a reply I made to a post from a month or so ago. Think it will help you out.

A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

Hope this helps.

Brent

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

DELETE TOP

Environment: SQL Server 2005
Language: T-SQL

I want to insert the top first record from the temp table '#NewTemp'
to the Employee table 1 record at a time until all the records from the temp table
have been copied.
After inserting the top first record into the Employee table, how can I retrive the identity key 'NewEmployeeID' from the Employee table?
I would then like to delete the top row in the #NewTemp table.
The process should be repeated until the @.Counter variable is 0.
Currently, I get an error msg 'Incorrect syntax near the keyword TOP'.What's wrong with the following T-SQL and how can I fix it? thanks.

BEGIN
DECLARE @.Counter as INT
SELECT @.Counter = COUNT(*) FROM #NewTemp
DECLARE @.TopCount AS INT
SET @.TopCount = 1

WHILE @.Counter > 0
BEGIN
INSERT INTO dbo.Employee(
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail)
SELECT TOP(@.TopCount)
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail
FROM dbo.#NewTemp

SELECT SCOPE_IDENTITY() AS NewEmployeeID

PRINT 'The count is ' + CONVERT (VARCHAR (10), @.Counter)

PRINT --NewEmployeeID

SET @.Counter = @.Counter -1

DELETE TOP(@.TopCount) from dbo.#NewTemp
END

DROP TABLE dbo.#NewTemp
END

I'm sure you have your reasons, but I have to say, this is very, very 'odd' indeed.

DELETE does NOT recognize TOP. You cannot [ DELETE TOP ]. In fact, if users are active in your database, a second query using TOP may not return the same row(s) as the first query. In your SELECT query, TOP without a WHERE criteria just doesn't make any sense.

Why go to so much trouble?

Let us know what you are attempting to accomplish and perhaps we can help you.

|||

What I'm trying to accomplish is this.

Copy each row from temp table to the Employee table. After inserting each record into the Employee table,

I would like to get the Identity key for the new record inserted from the Employee table. Thanks.

|||

You will need to use the new OUTPUT clause of the input statement. You cannot use either @.@.identity or scope_identity() when you are inserting multiple rows. Just a second and I will try to get you an example. Give a look to this article:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You ought to be able to do something like this to retrieve your list of identities:

create table #identityList (idKey integer)

insert into targetTable
select col1,
col2,
...
colN
output inserted.theIdentityColumn
into #identityList
from yourTempTable

-- to retrieve the identity values:
select idKey

from #identityList

Now, if you are going to need to match your identies back to the temp table that you inserted from you will also need to include column(s) in your identity list that make the inserted row unique.

|||

thanks, nice article. I think it will help. Is 'inserted' a reserved word in the code above?

In my code above, reason I have while loop and a counter is so I can go each record at time for insertion from the

temp table to the Employee table. What would be the alternative if using scope_identity() or @.@.identity?

|||

John,

In my earlier response, I asked if you would give us more detail information about what you are attempting to accomplish. It is not at all clear what is going on here.

If you only wanted to move the data from the #NewTemp table into the Employees table, it is as simple as an INSERT... SELECT.

Code Snippet

INSERT INTO dbo.Employee
( EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
)
SELECT
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
FROM dbo.#NewTemp

However, perhaps there is some thing you still haven't told us. What is your concern about the SCOPE_IDENTITY() or @.@.IDENTITY?

We can't do a good job of helping you unless you give us all of the information.

|||

create table dbo.#newTemp
( EmployeeFName varchar(20),
EmployeeLName varchar(20),
EmployeePhone varchar(14),
EmployeeEmail varchar(30)
)
insert into dbo.#newTemp
select 'Dave', 'Mugambo', '(800)555-1212', null union all
select 'Kent', 'Waldrop', '(800)555-1212', null union all
select 'Geoff', 'Waldmeyer', '(800)555-1212', null
select * from dbo.#newTemp
/*
EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- --
Dave Mugambo (800)555-1212 NULL
Kent Waldrop (800)555-1212 NULL
Geoff Waldmeyer (800)555-1212 NULL
*/

declare @.idList table (newEmployeeID int)

insert into dbo.Employee
output inserted.employeeId
into @.idList
select EmployeeFName,
EmployeeLname,
EmployeePhone,
EmployeeEmail
from dbo.#newTemp

-- --
-- If you need to see the new employee
-- information, you can you the
-- permanent table to the IDList like
-- this:
-- --

select EmployeeId,
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
from dbo.employee a
inner join @.idList b
on a.employeeId = b.newEmployeeId

/*
EmployeeId EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- -
1 Dave Mugambo (800)555-1212 NULL
2 Kent Waldrop (800)555-1212 NULL
3 Geoff Waldmeyer (800)555-1212 NULL
*/

go

drop table dbo.#newTemp

(edited the syntax; it was previously wrong)

|||

After filling the table variable with the inserted records,

how can I get each newEmployeeId one at a time. I would like to

pass this newEmployeeID to another sp one at a time.

thanks much.

|||

If this is what you must do, then use a cursor with a while loop -- but understand, cursors tend to be performance killers. A better approach is to design set based operations; it is what SQL server is best at. I will edit this with some posts that talk about the problems of cursors.

Here are some posts that comment on cursors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

I have many more examples in my list.

|||

If you really MUST do a row-wise operation, instead of a CURSOR, I recommend using the previous suggestion of the SQL 2005 OUTPUT keyword, outputing into a table variable, then adding an IDENTITY column to the table variable, and then using a WHILE loop to step through the rows in the table variable.

It will not be as resource intensive on the server, and most likely even execute faster than a CURSOR.

|||

As an aside, delete does recognize TOP in 2005. It is non ordered, so it doesn't make sense in this particular case. It's use is to batch deletes, like if you want to delete a large number of rows, but want to limit the pressure on the log, you can just delete them in batches over and over until all data is gone.

Code Snippet

create table #NewTemp
(
EmployeeFName varchar(30)
, EmployeeLName varchar(30)
, EmployeePhone varchar(30)
, EmployeeEmail varchar(30)
)

insert into #newTemp
select 'fred','flintstone','quarry 3424','fredf@.slate.com'
union
select 'barney','rubble','quarry 3452','barneyr@.slate.com'

delete top (1) from #newTemp

select *
from #newTemp

Delete this post

hello all,

I have the following script that does not work in windows XP SP2

//Script

sub UpLoad (UploadFileName)

dim b1

b1=&H4000000

set objStream =CreateObject("ADODB.Stream")

set objRecord =CreateObject("ADODB.Record")

set cnn =CreateObject("ADODB.Connection")

cnn.Open "provider=MSDAIPP.DSO;data source= http://192.168.0.100"

objRecord.Open UploadFileName,cnn, 3,b1

objStream.Type = 1 'adTypeBinary

objStream.Open objRecord,3,4 /////// Error line //////

objStream.LoadFromFile "c:\"+UploadFileName

objStream.Flush

objStream.Close

objRecord.Close

Set objStream = Nothing

Set objRecord = Nothing

cnn.close

set cnn = nothing

end sub

Error Message is "No such interface supported"

Any idea whoud be appreciated highly

Would this post be served better at a different forum?|||

Hi admin,

this post is not in a good forum so please delete it for me.

thanks, mahsina

delete the record i want

hi

i am using vb6 and access. i have a form in which there is a adodc, a datalist OR a datagrid to view the contents of a table in an access file. i want to delete a particular record by selecting the row (in a datagrid) OR selecting the record in a datalist. how do i achieve this? pl help

onilHave the on_click action call a stored procedure to delete the record. Just pass the record id to the stored procedure and let it do the delete. You'll then want to refresh the dataset and control.|||hi

i tried but it always deletes (in both cases -datagrid and datalist) the first record BUT NOT the record i click on

onil|||ONIL,
you may want to post your question in the Delphi, Visual Basic, C etc forum. It looks like your problem is VB related, not SQL. What you want to know is how to find out which line of the datagrid was clicked.|||hi

thanks for the interest. i have solved the issue using the seek command

onil

delete the databse

how can i delete the database if it is in loading state?

any helpHi there,

Which version of SQL are you working on? SQL 2000/2005 ? Detach the database first before deleting it. Hope it helps, good luck & take care.

Quote:

Originally Posted by bharadwaj

how can i delete the database if it is in loading state?

any help

|||Hi sashi,

we r usinq sql2000. logshipping is going on.we have remove the logshipping recently.that time we have done some mistake.now when we try to re-configure logshipping,we found that the database is there in secondary server (with loading state).

I have checked the following things.
1.attach\detach database.
2.take offline for that database
3. restore database with recovery.
4.sp_dbremove replication...
every time i was getting the error like the database is in use.
i have checked the msdb logshipping tables also.but no clue

any advice from ur side.
regards
Bharat

Delete Textfiles after dataload

I am transferring data from text file to sql server.I have created .dtsx packages. After the package executes i need to remove the data from the text file or even remove the text files. But i want my package to run it receives new textfile.What do i need to do?Please help?

The file system task on the control flow can delete the file for you.|||Thanks it worked...|||where does the deleted file get stored...|||If the files were good, they go to textfile and binary heaven. If the files were bad (corrupt), they might go to purgatory or worse |||

sureshv wrote:

where does the deleted file get stored...

Umm, they don't. They get deleted.

delete text character

hi,

for example ;

select price from table

query results:195dollar

I want to see query result only numeric:195

how can I do?

You could try this:

select convert(int, price) as price from table

That would round the value to the nearest integer value.

|||

Allen White wrote:

You could try this:

select convert(int, price) as price from table

That would round the value to the nearest integer value.

Don't working :(

Delete TempDB Database on SQL Server 2000 EE

I inherited a SQL Server 2000 Enterprise Edition database server that has
system databases that do appear in the SQL Server 2000 Enterprise Manager
GUI. How can get the system databases to appear in the SQL Server 2000
Enterprise Manager GUI? When I opened the Query Analyzer the system
databases appear.
Please help me resolve this issue.
Thanks,Right click on the name of the server and choose Edit SQL Server
Registration Properties. Check the box that says "Show system databases and
system objects"
Click OK
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,|||"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,|||"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,

Delete TempDB Database on SQL Server 2000 EE

I inherited a SQL Server 2000 Enterprise Edition database server that has
system databases that do appear in the SQL Server 2000 Enterprise Manager
GUI. How can get the system databases to appear in the SQL Server 2000
Enterprise Manager GUI? When I opened the Query Analyzer the system
databases appear.
Please help me resolve this issue.
Thanks,
Right click on the name of the server and choose Edit SQL Server
Registration Properties. Check the box that says "Show system databases and
system objects"
Click OK
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,
|||"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,
|||"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,

Delete Takes Time when u have text datatype column

I have a table with a coulumn of Text Datatype. This column is stored with XML data. Now i need to delete records from this table using a SP. The deletion 60K records is taking 30 min almost. Generally this table will not have this much deletion. But when there is no Text data type column (i removed the column from the table for testing) the same deletion is taking only few sec. What is the techinical reason behind it.

If anyone could explain this it would be really helpful

Leena

See Text datatype in Books Online. It is one of the BLOB datatype, it depends with the option you set in the table for large objects.

Micrsoft recommand to switch over from Text/Ntext to Varchar(Max)/NVarchar(Max) - if you use sql server 2005.

|||

It really is all depending on the size of the data in your text datatype. Data is stored in ~8K pages, and if you have 80K worth of text in columns, it will require 10 pages to be deleted to delete the row. Your table will look something like (and this can get ugly for 60K rows):

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

And all of the pages would have to be deleted. I think this might be improved upon performance wise in 2005, but I am not 100% sure. There is a concept of Ghost rows that may apply here where pages are simply marked as deleted. You should also try using the varchar(max) datatype if at all possible, though it has the same issues.

|||

thanks mani and louis for quick help.

one more q..... is sp_spaceused reflect the text data storage space also. If not how can we findout that.

the table with Text data and with out text data is almost showing same space used. Why its so..

Thanks again

Leena

|||

The large datatype value is stored outside of your table row & your current tables row's keeping the pointer of the outside stored values. To change this settins use the following statement

Exec sp_tableoption N'MyTable', 'large value types out of row', OFF

Exec sp_tableoption N'MyTable', 'text in row', ON

|||

When the BLOB's are not stored in row you have a lot of random I/O going on too.

Random I/O are very expensive of course. It has to check for each record where the BLOB data is located, remove it, go to the next record, check where the BLOB data is located, remove it, ... well you get my point I hope :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Delete takes extremly long time.

guys, im running MSSQL 2000 latest SP on PIII-1Ghz, 4GB RAM, SCSII and all good things but...

...when i execute this simple query to delete 8,000 records out of 18,000 total in the table it takes 11 seconds:

delete from tempViewResults where opportunity_id = '016158'

i do have an index on opportunity_id.
any ideas why? is this normal?

any help is greatly appreciated.please anybody?|||In this case, the index is not really helping you so much as hurting you. In order for the optimizer to consider using the index, you have to be going after <10% of the table. How many other indexes are there on the table? Also check for triggers on the table that may be firing for deletes.|||Also consider to create that "temp" table in the tempdb, which is faster.|||Two more cents:

Cent #1:Your delete statement will take longer if their are cascading relationships set up with subtables.

Cent #2:I'm not sure if using tempdb would be faster, because it still would be writing data to disk, but if you can get away with using a table variable which uses memory you might be able to avoid disk writes altogether.

blindman|||Using a #temp table is faster if your database is in FULL or BULK-LOGGED model.

Of course if your database is in SIMLE model you won't get any speed improvement|||Manowar:

What makes the temptable faster than a stored table? I couldn't find anything in Books Online regarding this. I'd have thought that querying a stored table would enable the optimizer to take advantage of cached query plans. Could you point me to where this is explained or documented in Books Online or Microsoft's support site?

blindman|||I think there's nothing about it on BOL or MSDN. I've found a note on Kalen Delaney "Inside SQL Server 2000" world-famous book.

You can look in the section about temp (#) tables. Kalen says that since in the tempdb sql server only register log data for rollback purposes (and not for database recovery), data modification operations can be up to for time faster that in a standard db.