Help On Trigger For Delete

I have a SQL statement that deletes a lot of records in a table (PACCESOS_DET) and a Trigger that fires for delete on the table.
The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message.
For each row deleted I need to update a column in another table (PACCESOS_CAB).
This the trigger...

CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS
declare @.mdias as int
declare @.mFKFeria as int
declare @.mtipo as char(1)
declare @.mfkcontacto as varchar(7)

if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)
begin
select @.mfkferia=m.fkferia, @.mfkcontacto = m.fkcontacto, @.mtipo = m.tipo, @.mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo
update PACCESOS_CAB set diasvisita = @.mdias -1 where FKFeria= @.mFKFeria and FKContacto=@.mFKContacto and Tipo=@.mTipo
end

Thanks in advanced."Inside every large program is a small program screaming to get out."

Your trigger is MUCH too complicated. Best I can figure, this is all you need:

CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS

begin
update PACCESOS_CAB
set PACCESOS_CAB.diasvisita = PACCESOS_CAB.diasvisita -1
from PACCESOS_CAB
inner join deleted
on PACCESOS_CAB.FKFeria = deleted.FKFeria
and PACCESOS_CAB.fkcontacto = deleted.fkcontacto
and PACCESOS_CAB.Tipo = deleted.Tipo
end|||It worked fine, sinco I keep on not Knowing the problem with the first solution.

anyway, lot of thanks blindman.|||Problems with your first solution:

This phrase is completely unnecessary, as records between the two tables will be matched in the UPDATE query:

"if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)"

These variables are not required, because as you can see from the solution all the records can be updated simultaneously with a single UPDATE statement, so it is not necessary to store values in temporary variables:

"declare @.mdias as int
declare @.mFKFeria as int
declare @.mtipo as char(1)
declare @.mfkcontacto as varchar(7)"

This statement fails when more than one record is deleted, because your FROM clause will return more than one record, and the parameters can hold only single values:

"select @.mfkferia=m.fkferia, @.mfkcontacto = m.fkcontacto, @.mtipo = m.tipo, @.mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo"

Help needed with trigger

Hi All,

I have this trigger wich runs good!

CREATE trigger trUpdateGEOData
on dbo.BK_Machine
after insert, update
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber >= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID

Now the thing is that a machine not neccesarily needs a location which mean that if postalcode or doornumber is NULL this trigger should clear the street and city columns.

Does someone have an Idea?

Cheers WimLOC_Street = case when insrted.PostalCode is Null or
inserted.DoorNumber is Null then '' else GEO_Postcode.STraatID end

Change your UPDATE Statment to include this. Do something similar with Loc_City.

Help needed with Instead of Update trigger on a View

I have an application where we are replacing a subsystem including portions
of the database. In order to minimize the code impact on the existing
application, we have decided to create a few "compatibility views" - i.e.
database Views that produce the same result and with the same names as the
old tables. Also in order to allow existing code to continue to function,
we are implementing INSTEAD OF triggers on the views. Even though all our
database accesses are encapsulated in stored procs, we have around 1500 of
them, and one of the tables we needed to reengineer this way is the "main"
table for the entire app. To make sure this isn't trivial, we have a new
"master" entity table with an Identity column that is referenced by the
reengineered "main" table.
At this point, we are only aware of performance impacts - everything appears
to work OK:
1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
statement) is part of an index, we loose the use of the index as a result of
having to use NULLIF() or COALESCE() on those columns in the view. For the
same reason, we can't index those view(s).
2. (This is where the question comes in:) The INSTEAD OF UPDATE trigger
appears to require a large number of separate UPDATE statements against the
base tables, or building a dynamic SQL statement. We are looking for
guidance...
Now to my question:
In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
table. If I understand correctly, since the triggering update statement may
only update one column, I cannot use an UPDATE statement against the base
table that updates all the columns with the values from the 'updated'
pseudo-table. Instead, I will have to check if each column is updated, and
if so, either update it separately or build a dynamic SQL UPDATE statement
including those columns that have been updated.
What is the recommended approach to this?
TIA,
Tore.On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
(snip)
>1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
>statement) is part of an index, we loose the use of the index as a result o
f
>having to use NULLIF() or COALESCE() on those columns in the view.
Hi Tore,
I don't think I understand what you're saying here. Where are you using
NULLIF() or COALESCE() and why? Coould you post a simplified sample of
your code?

> For the
>same reason, we can't index those view(s).
And neither should you. If you index the views, you'll create a complete
copy of your data. I don't think that yoou should do that in your
scenario.
(snip)
>In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
>table. If I understand correctly, since the triggering update statement ma
y
>only update one column, I cannot use an UPDATE statement against the base
>table that updates all the columns with the values from the 'updated'
>pseudo-table.
You undersatnd incorrectly. There is no 'updated' pseudo-table. The
'deleted' and 'inserted' pseudo-tables contain the complete before and
after image of the updated rows, including all columns that are not
affected by the update.
If your compatibility view translates to one new table, just perform the
modification in a single UPDATE statement. If your compatibility view
translates to more than one new table, use
IF UPDATE(col1) OR UPDATE(col2) ....
to find out which table(s) need updating, then use a single UPDATE
statement for all rows in each of the tables.
Sure, you'll be setting columns to the same value they already had. The
added cost of that is much less than the cost of finding out which
columns to update and executing up to 90 (!) consecutive UPDATE
statements against the same set of rows.

> Instead, I will have to check if each column is updated, and
>if so, either update it separately or build a dynamic SQL UPDATE statement
>including those columns that have been updated.
Using the dynamic SQL is even a worse option - it forces you to give
every user update permissions to the table. (And it will be slow because
of the extra recompiles).
www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVP|||Thanks Hugo,
I'll be looking at this tomorrow.
Tore.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:p75762lfnlmg789b20fv9jk1jfjrhs2it0@.
4ax.com...
> On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
> (snip)
of
> Hi Tore,
> I don't think I understand what you're saying here. Where are you using
> NULLIF() or COALESCE() and why? Coould you post a simplified sample of
> your code?
>
> And neither should you. If you index the views, you'll create a complete
> copy of your data. I don't think that yoou should do that in your
> scenario.
> (snip)
may
> You undersatnd incorrectly. There is no 'updated' pseudo-table. The
> 'deleted' and 'inserted' pseudo-tables contain the complete before and
> after image of the updated rows, including all columns that are not
> affected by the update.
> If your compatibility view translates to one new table, just perform the
> modification in a single UPDATE statement. If your compatibility view
> translates to more than one new table, use
> IF UPDATE(col1) OR UPDATE(col2) ....
> to find out which table(s) need updating, then use a single UPDATE
> statement for all rows in each of the tables.
> Sure, you'll be setting columns to the same value they already had. The
> added cost of that is much less than the cost of finding out which
> columns to update and executing up to 90 (!) consecutive UPDATE
> statements against the same set of rows.
>
statement
> Using the dynamic SQL is even a worse option - it forces you to give
> every user update permissions to the table. (And it will be slow because
> of the extra recompiles).
> www.sommarskog.se/dynamic_sql.html
> --
> Hugo Kornelis, SQL Server MVP

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) =
POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) =
> POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:

>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) = POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) => POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>> Hi All,
>> I need to write a trigger to catch all updates made to a table and insert
>> the old and new values into a new table.
>> The problem is that the table being "Audited" has a lot of fields in
>> (more than 120) and a trigger that was written for it takes too long to
>> execute.
>> Splitting the table up into smaller tables not an option right now
>> unfortunatly.
>> Is it possible to write an update trigger that can be fired and is clever
>> enough to only validate updated fields and still be as fast as possible.
>> Thanks
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:
>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
Thanks
Jason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) =
POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>
|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) =
> POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>
|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:

>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed on DDL Triggers in SQL server 2005

Hello,
I am trying to create DDL trigger as below
ALTER TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
/*POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
TARGETOBJNAME,
TARGETOBJTYPE,
TSQLSTATEMENT,
FULLDATA*/
)
VALUES
(
CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
/*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
@.EventData*/
)
But when i execute this its throwing an error...
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Am i doing some thing here?
Please help
Thanks
-LPerhaps you need
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME)
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
> Hello,
> I am trying to create DDL trigger as below
>
> ALTER TRIGGER DDLTRIGGER ON DATABASE
> FOR DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.EventData XML
> SET @.EventData = EVENTDATA()
> INSERT DDLEVENTLOG
> (EVENTTYPE
> /*POSTTIME,
> SPID,
> SERVERNAME,
> LOGINNAME,
> DATABASENAME,
> SCHEMANAME,
> OBJECTNAME,
> OBJECTTYPE,
> TARGETOBJNAME,
> TARGETOBJTYPE,
> TSQLSTATEMENT,
> FULLDATA*/
> )
> VALUES
> (
> CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
> /*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
> @.EventData*/
> )
> But when i execute this its throwing an error...
>
> Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
> "Query" is not a valid function, property, or field.
> Am i doing some thing here?
> Please help
> Thanks
> -L
>|||Please post the DDL for DDLEVENTLOG.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
Hello,
I am trying to create DDL trigger as below
ALTER TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
/*POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
TARGETOBJNAME,
TARGETOBJTYPE,
TSQLSTATEMENT,
FULLDATA*/
)
VALUES
(
CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
/*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
@.EventData*/
)
But when i execute this its throwing an error...
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Am i doing some thing here?
Please help
Thanks
-L|||Seems some of the XML and Xquery stuff is case sensitive. Below executed wit
h no errors on my
machine:
CREATE TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
)
VALUES
(
@.EventData.query('data(//EventType)')
--CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
> Hello,
> I am trying to create DDL trigger as below
>
> ALTER TRIGGER DDLTRIGGER ON DATABASE
> FOR DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.EventData XML
> SET @.EventData = EVENTDATA()
> INSERT DDLEVENTLOG
> (EVENTTYPE
> /*POSTTIME,
> SPID,
> SERVERNAME,
> LOGINNAME,
> DATABASENAME,
> SCHEMANAME,
> OBJECTNAME,
> OBJECTTYPE,
> TARGETOBJNAME,
> TARGETOBJTYPE,
> TSQLSTATEMENT,
> FULLDATA*/
> )
> VALUES
> (
> CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
> /*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
> @.EventData*/
> )
> But when i execute this its throwing an error...
>
> Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
> "Query" is not a valid function, property, or field.
> Am i doing some thing here?
> Please help
> Thanks
> -L
>|||Uri,
Thanks for the quick reply . But :( no luck yet.
After changing the whole line looks like this
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)')) as
sysname)
but it said
Msg 1035, Level 15, State 10, Procedure DDLTRIGGER, Line 23
Incorrect syntax near 'cast', expected 'AS'.
Am i doing it right here?
Thanks
-L|||Thank you for the quick reply. But i still get the same error on my
machine.
if i run your sql as is i got this
Msg 257, Level 16, State 3, Procedure DDLTRIGGER, Line 6
Implicit conversion from data type xml to nvarchar is not allowed. Use
the CONVERT function to run this query.
and later i uncommented the Convert line and commented out the first
line then i got the same Query error
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Do i need to setup any kind of option on my machine?
Thanks
-L|||You've misplaced the closing parentheses:
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)') as
sysname))
ML
http://milambda.blogspot.com/|||Looks like all of you have no problem running the above sql... But i
still get the same thig.
Here is the entire sql that i am trying to run
CREATE TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventData XML
SET @.eventData = EventData()
INSERT DDLEVENTLOG
(EVENTTYPE
)
VALUES
(
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)') as
sysname))
)
Some thing wrong with it?
I still get the Query error
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Thanks
-L|||FWIW, I use @.EventData.Value, not @.EventData.Query. E.g. these work great
for me in a DDL auditing trigger:
@.eventdata.value('(/EVENT_INSTANCE/SPID)[1]','int')
@.eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
@.eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)')|||I got it solved the line, CONVERT
(NVARCHAR(100),@.EventData.query('data(//EventType)')) works!
It doesn't recognise @.EventData.Query but it does @.EventData.query..
I thank all of you for looking into my problem.
How ever i need one more help with dropping a trigger
when i run this sql, drop trigger ddltrigger
i got this message. Is it some kind of security issue? what property do
i need to set inorder to drop it?
Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'ddltrigger', because it does not exist or you
do not have permission.
Thanks
-L

Help me write my first Update Trigger (sql svr 2000)

can someone help me write a Trigger? I have never written a trigger. This is for SQL Server 2000

Table FOO:
----
ID (numberic counter)
Status (Char)
etc..

Table BAR:
----
ID
Status
DateUpdated (getdate())

Whenever the Status in Table FOO is updated, I need to INSERT a new record into BAR with the ID and Status

~LeCREATE TRIGGER FOO_Update ON [FOO]
FOR Insert, Update
AS
Insert into Bar
(ID,
Status,
DateUpdated)
Select ID,
Status,
Getdate()
From inserted

...but you should really think of just adding the DateUpdated field to FOO with a default of getdate() for new records and having the trigger update it:

CREATE TRIGGER FOO_Update ON [FOO]
FOR Update
AS
Update FOO
set DateUpdated = Getdate()
From FOO
inner join inserted on FOO.ID = inserted.ID

blindman|||B.E.A.U.-tiful

Works Perfectly!

Thank you very very much!

~Le

Help me Please to Create this TRIGGER

Hi everybody,

How can I Update a field from another table by Trigger? Can someone send me the statment to do it?

I have a table called Clients with fields : ID_Clients, Client
And Another called Doc with fields : ID_Doc, ID_Clients, Client

These tables are in different databases and I would like to esure the integrity by add a Trigger to update in Docs table the field Client everytime its changed in the Clients table.

Thanks for Attetion.

Leonardo AlmeidaThis is not a forum for tutorials.

Here is some sample code:

CREATE TRIGGER YourTrigger ON dbo.YourTable
FOR UPDATE
AS
update DBName.DBTable
set YourField = inserted.YourField
from DBName.DBTable
inner join inserted on DBName.DBTable.KeyField = inserted.KeyField

Please read about triggers in Books Online and then post again if you have a specific question.

blindman

Help me create this Trigger

Hi everybody,

How can I Update a field from another table by Trigger? Can someone send
me the statment to do it?

I have a table called Clients with fields : ID_Clients, Client
And Another called Doc with fields : ID_Doc, ID_Clients, Client

These tables are in different databases and I would like to esure the
integrity by add a Trigger to update in Docs table the field Client
everytime its changed in the Clients table.

Thanks for Attetion.

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Leonardo Almeida" <leonardoalmeida2004@.yahoo.com.br> wrote in message
news:3f672aa2$0$62077$75868355@.news.frii.net...
>
> Hi everybody,
> How can I Update a field from another table by Trigger? Can someone send
> me the statment to do it?
> I have a table called Clients with fields : ID_Clients, Client
> And Another called Doc with fields : ID_Doc, ID_Clients, Client
> These tables are in different databases and I would like to esure the
> integrity by add a Trigger to update in Docs table the field Client
> everytime its changed in the Clients table.
> Thanks for Attetion.
> Leonardo Almeida
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Something like this should work:

create trigger dbo.ATR_U_Clients
on dbo.Clients
after update
as

if @.@.rowcount = 0
return

update OtherDatabase.dbo.Doc
set Client = i.Client
from OtherDatabase.dbo.Doc d
join inserted i
on d.ID_Clients = i.ID_Clients

Simon