Creating DB from scripts: trigger

Showing posts with label trigger. Show all posts

Thursday, March 29, 2012

Creating an INSERT UPDATE Trigger that works on certain account

Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> the proprietary database account. How do I capture the user or group name in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> What is the equivalent in MS SQL? Thanks in advance!!!|||This is a multi-part message in MIME format.
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the =currently logged in user account.
Look in Books-on-Line for more information on system_user.
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message =news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database server will =
> occassionally push data back into MS SQL via JDBC. I don't want the MS =SQL > trigger to execute its SQL code if the INSERT UPDATE Trigger was =initiated by > the proprietary database account. How do I capture the user or group =name in > MS SQL? I know in PostgreSQL, you can use the "user" variable in a =trigger. > What is the equivalent in MS SQL? Thanks in advance!!!
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try the sytem function: system_user. =
For example, SELECT system_user will provide the =Domain\Loginname of the currently logged in user account.
Look in Books-on-Line for more =information on system_user.
-- Arnie Rowland, YACE* "To be =successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Jaime Rios" wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...> =Hi folks,> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database =server will > occassionally push data back into MS SQL via JDBC. I don't want =the MS SQL > trigger to execute its SQL code if the INSERT UPDATE =Trigger was initiated by > the proprietary database account. How do I capture =the user or group name in > MS SQL? I know in PostgreSQL, you can use =the "user" variable in a trigger. > What is the equivalent in MS SQL? =Thanks in advance!!!

--=_NextPart_000_0EFB_01C6952D.3CD86780--|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>> Hi folks,
>> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL
>> 2000
>> to another proprietary database server. The other database server will
>> occassionally push data back into MS SQL via JDBC. I don't want the MS
>> SQL
>> trigger to execute its SQL code if the INSERT UPDATE Trigger was
>> initiated by
>> the proprietary database account. How do I capture the user or group name
>> in
>> MS SQL? I know in PostgreSQL, you can use the "user" variable in a
>> trigger.
>> What is the equivalent in MS SQL? Thanks in advance!!!|||I concur with Arnie.
--
Venkat
sql server admirer
"Arnie Rowland" wrote:
> Try the sytem function: system_user.
> For example, SELECT system_user will provide the Domain\Loginname of the currently logged in user account.
> Look in Books-on-Line for more information on system_user.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> > Hi folks,
> > I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> > to another proprietary database server. The other database server will
> > occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> > trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> > the proprietary database account. How do I capture the user or group name in
> > MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> > What is the equivalent in MS SQL? Thanks in advance!!!

Creating an INSERT UPDATE Trigger that works on certain account

just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>|||Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated b
y
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!|||just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>sql

Creating an Audit trail on a table using a trigger

Hi,
This is kind of following on from my last couple of posts regarding Identity
columns and so on.
Basically, I want to ensure that for a particular table, every row has a
numeric reference. This reference must be unique and gapless. Ideally it
should order in the sequence of the records being inserted however this
isn't an absolute requirement.
From my (limited) understanding of SQLS, I think I can achieve this with a
FOR INSERT Trigger - in that the trigger is fired every time a row is
inserted and the trigger is the same transaction as the initial insert hence
I avoid any concurrency issues.
However I'm not completely sure how to achieve this. I think that my trigger
should be along the lines of this...
CREATE TRIGGER AssignAuditReference ON tblBooking
FOR INSERT
AS
DECLARE @.Ref int
--Get the highest reference and add one.
SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
--Update the inserted row to have a booking_referecen of the new reference
obtained above.
Update tblBooking
SET Booking_Reference = @.ref
WHERE Booking_ID = INSERTED.Booking_ID
However I'm getting problems with the INSERTED table not being recognised.
I understood that the INSERTED table contained the row that the insert that
started the trigger inserted.
Two questions:
1. Where am I going wrong with my trigger. Have I misunderstood some key
point of using triggers.
2. Is this the right approach to achieve what I am after? Are there any
better approaches...Hi Chris,
You have to mention the Inserted Table in your Update Query
Update tblBooking
SET tblBooking.Booking_Reference = @.Ref
FROM tblBooking
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
Because of the lack between getting the @.Ref-Value and writing it in the
table i would prefer an inline Query and Update
Update tblBooking
SET tblBooking.Booking_Reference = NewJobIdTable.NewJobId
FROM tblBooking,
(
Select ISNULL(MAX(job_id),0)+1 AS NewJobId from tblBooking
) NewJobIdTable
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Chris Strug" <hotmail@.solace1884.com> schrieb im Newsbeitrag
news:eBcd9GOQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is kind of following on from my last couple of posts regarding
> Identity
> columns and so on.
> Basically, I want to ensure that for a particular table, every row has a
> numeric reference. This reference must be unique and gapless. Ideally it
> should order in the sequence of the records being inserted however this
> isn't an absolute requirement.
> From my (limited) understanding of SQLS, I think I can achieve this with a
> FOR INSERT Trigger - in that the trigger is fired every time a row is
> inserted and the trigger is the same transaction as the initial insert
> hence
> I avoid any concurrency issues.
> However I'm not completely sure how to achieve this. I think that my
> trigger
> should be along the lines of this...
> CREATE TRIGGER AssignAuditReference ON tblBooking
> FOR INSERT
> AS
> DECLARE @.Ref int
> --Get the highest reference and add one.
> SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
> --Update the inserted row to have a booking_referecen of the new
> reference
> obtained above.
> Update tblBooking
> SET Booking_Reference = @.ref
> WHERE Booking_ID = INSERTED.Booking_ID
> However I'm getting problems with the INSERTED table not being recognised.
> I understood that the INSERTED table contained the row that the insert
> that
> started the trigger inserted.
> Two questions:
> 1. Where am I going wrong with my trigger. Have I misunderstood some key
> point of using triggers.
> 2. Is this the right approach to achieve what I am after? Are there any
> better approaches...
>|||Syntactically, your UPDATE statement is missing the FROM clasue:
UPDATE tblBooking
SET Booking_Reference = @.ref
FROM tblBooking , INSERTED
WHERE Booking_ID = INSERTED.Booking_ID
1) This will FAIL if more than one row is inserted - not a good idea
for maintaining an audit trail. 2) I don't see what advantage this has
over the more concise and reliable solution(s) already discussed in
your earlier threads. For example:
INSERT INTO tblBooking (booking_reference, x, y, z, ...)
SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
FROM tblBooking
IMO an incrementing counter is a poor way to maintain an audit trail
anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
preserve the history of changes to rows? This is easy to do in triggers
or in your data access code and doesn't suffer the inevitable and
serious blocking problems that your approach implies.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113478283.991829.222620@.g14g2000cwa.googlegroups.com...
> Syntactically, your UPDATE statement is missing the FROM clasue:
> UPDATE tblBooking
> SET Booking_Reference = @.ref
> FROM tblBooking , INSERTED
> WHERE Booking_ID = INSERTED.Booking_ID
> 1) This will FAIL if more than one row is inserted - not a good idea
> for maintaining an audit trail. 2) I don't see what advantage this has
> over the more concise and reliable solution(s) already discussed in
> your earlier threads. For example:
> INSERT INTO tblBooking (booking_reference, x, y, z, ...)
> SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
> FROM tblBooking
> IMO an incrementing counter is a poor way to maintain an audit trail
> anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
> preserve the history of changes to rows? This is easy to do in triggers
> or in your data access code and doesn't suffer the inevitable and
> serious blocking problems that your approach implies.
> --
> David Portas
> SQL Server MVP
> --
>
First of all thanks to both David and Jens for their replies.
Apologies for repeating myself, I just want to make sure that I understand
what I'm doing rather than repeating it parrot fashion into my database.
Regarding the trigger, I was under the impression that the trigger would
occur for every new row, I gather that it in fact applies to every INSERT.
Ahh... That makes things clearer.
Regards the actual implementation (TIMESTAMP vs. numeric reference),
unfortunately this is out of my hands. I've been informed that this a is a
non negotiatable requirement. What can you do?
if I may ask one more question, assuming that I did attempt to implement my
apprioach using triggers, could you expand on the blocking problems that you
would expect me to face?
Anyway, thank you once again for taking the time to help me, I do appreciate
it.
Regards
Chris.

Sunday, March 25, 2012

Creating a Trigger which updates a linked server

Hi all,
I read some message in FAQ about my problem but i doesn't work any
more...
I have 2 SQL-Servers that use sql-server and windows security
integrity.
On first Server, i add a linkServer to the other by using
sp_addlinkedserver and use a specified user ...
So i can use select command, execute store procedure, update , insert
and any command to read,update or write data .
I said Coool :-) I could put arrival data from one database to the
other database by using a trigger !!!
Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
When the trigger is firing, and only when i want to execute the insert
command, anything happen ... Also the @.@.error don't work...
Let me show what happen in the trigger :
CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
AFTER INSERT
AS
DECLARE ...
SET XACT_ABORT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET implicit_transactions off
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT ...
FROM OPENXML (@.idoc, '/EXECID',2)
WITH ( ... )
EXEC sp_xml_removedocument @.idoc
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
TOTO')
SET @.v_StrMess = 'The problem is HERE ...'
--raiserror (@.v_StrMess , 16 , 1)
COMMIT TRAN
if @.@.error >0
BEGIN
raiserror (@.v_StrMess , 16 , 1)
END
SET implicit_transactions on
END
I know that it's possible to update a linkedserver whith a trigger but
i don't know how !!!
Please, could you explain in good word how to do it or is there
another solution ?
Thx for answers ...
ByeWhat is the error you are getting?
AMB
"Mick" wrote:

> Hi all,
> I read some message in FAQ about my problem but i doesn't work any
> more...
> I have 2 SQL-Servers that use sql-server and windows security
> integrity.
> On first Server, i add a linkServer to the other by using
> sp_addlinkedserver and use a specified user ...
> So i can use select command, execute store procedure, update , insert
> and any command to read,update or write data .
> I said Coool :-) I could put arrival data from one database to the
> other database by using a trigger !!!
> Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
> When the trigger is firing, and only when i want to execute the insert
> command, anything happen ... Also the @.@.error don't work...
> Let me show what happen in the trigger :
> CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
> AFTER INSERT
> AS
> DECLARE ...
> SET XACT_ABORT ON
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SET implicit_transactions off
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT ...
> FROM OPENXML (@.idoc, '/EXECID',2)
> WITH ( ... )
> EXEC sp_xml_removedocument @.idoc
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
> TOTO')
> SET @.v_StrMess = 'The problem is HERE ...'
> --raiserror (@.v_StrMess , 16 , 1)
> COMMIT TRAN
> if @.@.error >0
> BEGIN
> raiserror (@.v_StrMess , 16 , 1)
> END
> SET implicit_transactions on
>
> END
>
>
> I know that it's possible to update a linkedserver whith a trigger but
> i don't know how !!!
> Please, could you explain in good word how to do it or is there
> another solution ?
> Thx for answers ...
> Bye
>|||@.@.Error is only captured for the very LAST executed statement. In your case,
you would always be 0.
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
SET @.v_StrMess = 'The problem is HERE ...' -- <++++You reset the
@.@.error value here
You should change it to this to capture the @.@.error and set your @.msg
declare @.err int
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
SELECT @.err=@.@.error, @.v_StrMess = 'The problem is HERE ...'
IF @.err=0
COMMIT
ELSE
BEGIN
ROLLBACK
raiserror (@.v_StrMess , 16 , 1)
END
-oj
"Mick" <mickymickmc@.yahoo.fr> wrote in message
news:79cab08b.0502151254.3b8d3d9a@.posting.google.com...
> Hi all,
> I read some message in FAQ about my problem but i doesn't work any
> more...
> I have 2 SQL-Servers that use sql-server and windows security
> integrity.
> On first Server, i add a linkServer to the other by using
> sp_addlinkedserver and use a specified user ...
> So i can use select command, execute store procedure, update , insert
> and any command to read,update or write data .
> I said Coool :-) I could put arrival data from one database to the
> other database by using a trigger !!!
> Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
> When the trigger is firing, and only when i want to execute the insert
> command, anything happen ... Also the @.@.error don't work...
> Let me show what happen in the trigger :
> CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
> AFTER INSERT
> AS
> DECLARE ...
> SET XACT_ABORT ON
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SET implicit_transactions off
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT ...
> FROM OPENXML (@.idoc, '/EXECID',2)
> WITH ( ... )
> EXEC sp_xml_removedocument @.idoc
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
> TOTO')
> SET @.v_StrMess = 'The problem is HERE ...'
> --raiserror (@.v_StrMess , 16 , 1)
> COMMIT TRAN
> if @.@.error >0
> BEGIN
> raiserror (@.v_StrMess , 16 , 1)
> END
> SET implicit_transactions on
>
> END
>
>
> I know that it's possible to update a linkedserver whith a trigger but
> i don't know how !!!
> Please, could you explain in good word how to do it or is there
> another solution ?
> Thx for answers ...
> Bye|||Thx for your answer ...
But the problem is not the @.@.error message or else !!!
The problem is that when the insert instruction start, nothing happen
...
I run a trace to understand what sql-server that run trigger do but
when the BEGIN DISTRIBUTED TRANSACTION start , there is the end of
transaction and nothing else.
For example, when i make an insert into srvdb1 by using sql-query
analyser, the trigger start and i wait until the connection is
broken...
i will test something and go back later ...
"oj" <nospam_ojngo@.home.com> wrote in message news:<eruW3o7EFHA.2176@.TK2MSFTNGP15.phx.gbl>.
.
> @.@.Error is only captured for the very LAST executed statement. In your cas
e,
> you would always be 0.
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
> SET @.v_StrMess = 'The problem is HERE ...' -- <++++You reset the
> @.@.error value here
> You should change it to this to capture the @.@.error and set your @.msg
> declare @.err int
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
> SELECT @.err=@.@.error, @.v_StrMess = 'The problem is HERE ...'
> IF @.err=0
> COMMIT
> ELSE
> BEGIN
> ROLLBACK
> raiserror (@.v_StrMess , 16 , 1)
> END
>
> --
> -oj
>
> "Mick" <mickymickmc@.yahoo.fr> wrote in message
> news:79cab08b.0502151254.3b8d3d9a@.posting.google.com...

Creating a trigger using a cursor

Hi all,
I need to create a trigger on all tables in a database that will insert into
an audit table username, and event on the table. I can create the trigger
individually, but I would like to put this into a cursor so I do not have to
run the trigger 500 times.
I am grabbing all user tables and trying to exec a string within the cursor
to create the triggers. I keep gettin eror by kyword insert. which I believe
is near
" INSERT INTO #inputbuffer"
Below is the code I am using:
TIA,
Joe
declare @.name varchar(100), @.str varchar(8000)
declare crscall cursor for
select name from sysobjects
where type = 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status = 0
begin
declare @.str varchar(8000),@.name varchar(50)
set @.name = 'testrights'
select @.str = 'IF EXISTS (SELECT name FROM sysobjects
WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
'FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll
deallocate crsCAllAre you sure you really want this kind of automation?
Anyway, change the script to print out the query strings instead of just
executing them. Then test them: parse them and attempt to execute them.
And when it's done - I don't want to scare you - you'll still have to test
them 500 times.
ML|||Thank you daniel,
I guess it was just an extra pair of eyes!
The first typo did the trick.
Thanks again.
Joe|||Thank you! this was helpful as well as Daniels.|||This is a one-time thing to create all the triggers so it won't get into
production code.
So good for the poster if he can automate the creation of the triggers.
But I agree with out on the last part, he's still have to test them all.
Maybe he can automate that part too. ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:8C32588A-D032-40D3-B554-D6A64EB83F15@.microsoft.com...
> Are you sure you really want this kind of automation?
> Anyway, change the script to print out the query strings instead of just
> executing them. Then test them: parse them and attempt to execute them.
> And when it's done - I don't want to scare you - you'll still have to test
> them 500 times.
>
> ML|||If he puts his mind to it, someday his entire life might get automated. :)
He'll have automated himself out of existence.
ML|||That's what I am looking for. Automation is a wonderful thing!|||Well, I wish you good luck on your journey. :)
I hope those 500 tables weren't created automatically by mistake... ;)
ML|||jaylou wrote on Thu, 28 Jul 2005 07:01:13 -0700:

> Hi all,
> I need to create a trigger on all tables in a database that will insert
> into an audit table username, and event on the table. I can create the
> trigger individually, but I would like to put this into a cursor so I do
> not have to run the trigger 500 times.
> I am grabbing all user tables and trying to exec a string within the
> cursor to create the triggers. I keep gettin eror by kyword insert. which
> I believe is near
> " INSERT INTO #inputbuffer"
> Below is the code I am using:
Did you copy and paste that code? If so, there are 2 errors I spotted
straight away, both near the word INSERT. Comments inline, look for Typo #1
and Typo #2.
Dan

> TIA,
> Joe
> declare @.name varchar(100), @.str varchar(8000)
> declare crscall cursor for
> select name from sysobjects
> where type = 'u'
> open crscall
> fetch next from crscall
> into @.name
> while @.@.Fetch_Status = 0
> begin
> declare @.str varchar(8000),@.name varchar(50)
> set @.name = 'testrights'
> select @.str = 'IF EXISTS (SELECT name FROM sysobjects
> WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
> '+''''+'TR'+''''+')
> DROP TRIGGER Audit_InsUpd'
> exec (@.str)
> select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
> ON '+@.name+
> 'FOR INSERT, UPDATE AS
Typo #1. There's no space between ' and FOR, so you'd end up with invalid
syntax here as the table name will be concatenated into FOR and then the
INSERT keyword is invalid as there is no FOR.

> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
> ' INSERT INTO #inputbuffer
Typo #2. There's a ' missing at the start of this line, so this INSERT won't
be inside the string being assigned to @.str, it's going to be run in the
trigger creating code and #inputbuffer doesn't yet exist as a table.
However, I'm pretty sure the error is due to typo #1 otherwise you'd have
received an error about table #inputbuffer not existing, the compiler might
not be getting this far.

> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> insert into Tbl_MSDBAudit
> select SUSER_SNAME(),@.qry
> END'
> select @.str
> exec (@.str)
> fetch next from crscall
> into @.name
> end
> close crsCAll
> deallocate crsCAll
>|||Hi
Run this Code
declare @.str varchar(8000),@.name varchar(50)
declare crscall cursor for
select name from sysobjects
where type =3D 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status =3D 0
begin
--set @.name =3D 'testrights'
select @.str =3D 'IF EXISTS (SELECT name FROM sysobjects
WHERE name =3D '+''''+@.name+'_Audit_InsUpd'+'=AD'''+' AND type =3D
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str =3D 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
' FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr =3D '+''''+'DBCC INPUTBUFFER('+
STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry =3D (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll=20
deallocate crsCAll=20
With warm regards
Jatinder Singhsql

Creating a Trigger that emails when new record is added.

I'm hoping one of you will be able to help me because I haven't had very good luck finding any information on this.

I'm kind of new to SQL, but I'm learning as much as I can.

I created a web form that sends a new record to a SQL 2005 table I setup.

This all works exactly as it should, but I would like to have an email sent out every time a record is added to this table.

I have SQL Mail setup and I ran a test and it worked, but I can't seem to find any info on how to create a trigger that will send an email to me when a new record is added to the table.

My Database is called Engineering

The table is called ESSPartNumLog

And I have the following Columns that I would like to send in my email..

ESSSequence (PK,int, not null)

MaterialType (nvarchar(255, null)

ESSPrefix (nvarchar(255, null)

PartDescription (nvarchar(255, null)

Project (nvarchar(255, null)

PM (nvarchar(255, null)

Any ideas, or can you point me in the right direction?

Hello,

Try this:

create trigger tr_ESSPartNumLog_Insert on dbo.ESSPartNumLog

for insert

declare @.id as integer

declare @.q as varchar(255)

--Get ID of record inserted.

select @.id = ESSSequence from inserted

--Create query string to return the record

select @.q = 'select ESSSequence, MaterialType, ESSPrefix, PartDescription, Project, PM

from ESSPartNumLog

where ESSSequence = ' + convert(varchar(10), @.id)

exec msdb.dbo.sp_send_dbmail

@.recipients = 'your_email@.domain.com',

@.subject = 'New record added in ESSPartNumLog',

@.query = @.q,

@.execute_query_database = 'Engineering'

Hope this helps.

Jarret

Creating a Trigger on Table Access

Creating a Trigger on Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.I don't think a 'SELECT' trigger would be a good idea since data could be
access for reasons other than login. IMHO, a stored procedure would be the
best approach since you can include other login activities, like password
validation. For example:
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30)
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<iamalex84@.gmail.com> wrote in message
news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Thank you very much. I have customized and created the procedure. One
more problem though... How do I use it in ASP? I have never worked
with stored procedures before. I'm having trouble with passing the
username and password to it. Also, how do I retrieve the returned
values? Also, is there a way to grab the current value before it is
updated?
Dan Guzman wrote:[vbcol=seagreen]
> I don't think a 'SELECT' trigger would be a good idea since data could be
> access for reasons other than login. IMHO, a stored procedure would be th
e
> best approach since you can include other login activities, like password
> validation. For example:
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30)
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <iamalex84@.gmail.com> wrote in message
> news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...|||> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
Below is a VBScript example as well as a proc that will return data via an
output parameter.
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30),
@.LastLoginTime datetime OUTPUT
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET
@.LastLoginTime = LastLoginTime,
LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
'methods to include ADO constants in ASP
connection.Open connectionString
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandType = adCmdStoredProcedure
command.CommandText = "dbo.usp_LoginUser"
Set returnCodeParameter = command.CreateParameter( _
"@.ReturnCode", adInteger, adParamReturnValue)
command.Parameters.Append returnCodeParameter
Set userNameParameter = command.CreateParameter( _
"@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
command.Parameters.Append userNameParameter
Set passwordParameter = command.CreateParameter( _
"@.Password", adVarChar, adParamInput, 30, Request("Password"))
command.Parameters.Append passwordParameter
Set lastLoginTimeParameter = command.CreateParameter( _
"@.LastLoginTime", adDBTimeStamp, adParamOutput)
command.Parameters.Append lastLoginTimeParameter
command.Execute()
If returnCodeParameter.Value = 0 Then
Response.Write "Login succeeded. Last login was " &
lastLoginTimeParameter.Value
Else
Response.Write "Login failed"
End If
connection.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <iamalex84@.gmail.com> wrote in message
news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...
> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
>
> Dan Guzman wrote:
>|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...sql

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit table
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> > Good Day All,
> >
> > I am trying to create a trigger on a table and this trigger must update
> >
> > an Audit table which reflects the column name (the changes apply to),
> > the old value and the new value.
> >
> >
> > I have tried running through a cursor to dynamically update the Audit
> > table with the individual fields but this does not work since when
> > selecting from the inserted or deleted table one can either select all
> > fields or certain fields but I find it difficult to select only values
> > for the field that is current on my cursor.
> >
> >
> > I really will appreciate your help.
> >
> >
> > Regards,
> > Phonzo.
> >

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:[vbcol=seagreen]
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit tabl
e
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
Try:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>
|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tuxTry:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>

Creating A Trigger

Hallo,
I am new on this forum but I have an urgent problem. How can I

1. Create a trigger which has the following characteristics:
It should be named reorder.
It should fire after an update of the qty column on the stock table, if the new
value of qty is <= 5.
New should be referenced as n.
The triggered action should insert the values n.itemno and current timestamp
into the reorder table,
whereby when an inventory item in the STOCK table falls below a quantity of 6, the REORDER table will have a row inserted.

Any kind of help is very much appreciated.

Quote:

Originally Posted by JKAG

Hallo,
I am new on this forum but I have an urgent problem. How can I

Any kind of help is very much appreciated.

hi jkag,

Check out the Trigger i have developed in sqlserver 2000.

sample tables used:
create table stock
(
itemid int identity(1,1) primary key,
prdname varchar(100),
qty int,
)

create table reorder
(
reoid int identity(1,1) primary key,
itemid int,
prdname varchar(100),
qty int,
currdate timestamp
)

create Trigger ReorderTrigg
on stock
for update
as
begin
insert into reorder(itemid,prdname,qty) select itemid,prdname,qty from stock where qty<=5
end
go

Go through the trigger and do let me know if it has solved ur query. If not also, pm me your problem. I shall try it. Gud Luck!

cheers,
jaisql

Creating a Trigger

I have 3 tables on my db, Projects, ProljectAllocationLog and Users

Project consists of Projectid(PK), ProjectName, UserID
ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, UserID,Date
Users consists of UserID (PK), Fullname

Over the course of time the user allocated to a project may change. The db has been set up to always show the most current user in the UserID of the Projects table,

I want to create a log that will record everytime that user has changed. (ProjectAllocationLog)

Having read through some examples posted on Forums, I believe that I can do this with a trigger, but I am not sure if I am doing it right, the trigger I have written is

Create Trigger tr_UpdateAllocationLog
ON Projects
AFTER Update
AS
If NOT UPDATE (Userid)

DECLARE @.PROJECTID
DECLARE @.NEWUSER
DECLARE @.PREVIOUSUSER

SET @.PROJECTID= (SELECT projected FROM Inserted)
SET @.NEWUSER = (SELECT UserID from Inserted)
SET @. PREVIOUSUSER = (SELECT UserID from Deleted)

If @.NEWUSER <> @.PREVIOUSUSER

INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) VALUES (@.PROJECTID, @.NEWUSER, GETDATE())

Go

I would appreciate any commentssomething like this is a little more graceful and handles multiple updates...

CREATE TRIGGER dbo.trg_ProjectStatusHistory
ON dbo.Project
FOR INSERT, UPDATE
AS
INSERT INTO ProjectStatusHistory(ProjectID,StatusID,SystemStat usID,DateChanged,UpdatedByUIDKey)
SELECT i.ProjectId,i.StatusId,i.SystemStatusId,GETDATE(), i.UpdatedByUIDKey
FROM inserted i
LEFT JOIN deleted d
ON i.ProjectId = d.ProjectID
WHERE i.StatusId <> d.StatusId OR i.SystemStatusId <> d.SystemStatusId
OR (i.StatusId is not null and d.Statusid is null)|||Briliant, thank you very much, I will do it the way you suggested, however, just out of curiosty, would my way have worked even though it was a very clumsey way of doing it?|||yours would not have worked for transactions that updated multiple rows and my memory is a little cloudy on this because I do not code a lot of triggers but I think I had a problem once calling records from one of the virtual tables and then calling records from another like you did here...

SET @.NEWUSER = (SELECT UserID from Inserted)
SET @. PREVIOUSUSER = (SELECT UserID from Deleted)

This might not work but I am not sure why. It was a while ago and if something happened more 3 days ago it might as well have never happened at all.|||That will only pull the first ID from inserted, and the first ID from deleted, and there is no guarantee that the two virtual tables will even be sorted identically.

You MUST use set-based operations in triggers.|||I dont understand what you mean by set based operations, can you explain please|||do not mind the blind dude. it's time for his medication.

the trigger in my first post is set based. he did not see it.|||but what does set based mean exactly? (Sorry to sound such a thicko)|||google "set based thinking"

here's one of many links:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx|||This is funny

I just had a request to build a "Project" app

I was so disdaned by the fact that I billed them a boatload and told them to buy MS Project and to send the users to class|||and I bet nobody blinked twice.|||do not mind the blind dude. it's time for his medication.

the trigger in my first post is set based. he did not see it.

I was referring to this:
SET @.NEWUSER = (SELECT UserID from Inserted)
SET @. PREVIOUSUSER = (SELECT UserID from Deleted)
And somebody better be minding the blind dude. Who's turn is it today, anyway? 'Cause nobody made me breakfast.

Creating a trigger

Hi all,
I've never played with triggers before but I need to create one, so I guess
I need some help!!
I've got a table TABLE1 in DATABASE1 and it has 2 fields MYID and MYNAME.
This is where the trigger needs to be created. MYID refers to a field in
another table in another database.
I need a trigger to update (on insert only) MYNAME according to what MYID is
by looking into the other table and retrieving the value.
Should be easy for the gurus in here!!
Any ideas?
Thanks,
IvanUntested:
USE Database1
GO
CREATE TRIGGER trg_table1 ON Table1
FOR INSERT
AS
UPDATE Table1
SET myname =
(SELECT myname
FROM database2.dbo.table1 AS T
WHERE myid = Table1.myid)
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE myid = Table1.myid)
Perhaps a better option though would be to create a view instead:
CREATE VIEW table1_with_name
(myid,myname)
AS
SELECT T1.myid, COALESCE(T1.myname,T2.myname)
FROM database1.dbo.Table1 AS T1
LEFT JOIN database2.dbo.Table1 AS T2
ON T1.myid = T2.myid
Either way, it can be useful to indirect all external database
references through views because it reduces the number of things that
need changing if you decide to relocate a database.
David Portas
SQL Server MVP
--|||Try,
create trigger dbo.tr_table1 on dbo.table1
for insert
as
set nocount on
update dbo.table1
set myname = (select a.myname from database2..tablex as a where a.myid =
table1.myid)
where exists(select * from inserted as i where i.myid = table1.myid)
AMB
"Ivan Debono" wrote:

> Hi all,
> I've never played with triggers before but I need to create one, so I gues
s
> I need some help!!
> I've got a table TABLE1 in DATABASE1 and it has 2 fields MYID and MYNAME.
> This is where the trigger needs to be created. MYID refers to a field in
> another table in another database.
> I need a trigger to update (on insert only) MYNAME according to what MYID
is
> by looking into the other table and retrieving the value.
> Should be easy for the gurus in here!!
> Any ideas?
> Thanks,
> Ivan
>
>|||Thanks :)
Ivan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1114524112.314396.275370@.g14g2000cwa.googlegroups.com...
> Untested:
> USE Database1
> GO
> CREATE TRIGGER trg_table1 ON Table1
> FOR INSERT
> AS
> UPDATE Table1
> SET myname =
> (SELECT myname
> FROM database2.dbo.table1 AS T
> WHERE myid = Table1.myid)
> WHERE EXISTS
> (SELECT *
> FROM Inserted
> WHERE myid = Table1.myid)
> Perhaps a better option though would be to create a view instead:
> CREATE VIEW table1_with_name
> (myid,myname)
> AS
> SELECT T1.myid, COALESCE(T1.myname,T2.myname)
> FROM database1.dbo.Table1 AS T1
> LEFT JOIN database2.dbo.Table1 AS T2
> ON T1.myid = T2.myid
> Either way, it can be useful to indirect all external database
> references through views because it reduces the number of things that
> need changing if you decide to relocate a database.
> --
> David Portas
> SQL Server MVP
> --
>

Wednesday, March 21, 2012

Creating a simple update trigger

I am extremely new at SQL Server2000 and t-sql and I'm looking to
create a simple trigger. For explanation sake, let's say I have 3
columns in one table ... Col_1, Col_2 and Col_3. The data type for
Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on
Col_2 to compare Col_1 to Col_2 when Col_2 is updated and if they're
the same, set the value on Col_3 to "Completed". Can someone please
help me?

Thanks,
JustinHi

Posting DDL (Create table statements etc.) and example data (as Insert
statements) removes any ambiguity. Within the trigger try something like:

UPDATE t
SET Col_3 = "Completed"
FROM MyTable t JOIN INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE i.col2 <> d.col2
AND i.col2 = i.col1

John

"Justin" <jhosman@.numc.edu> wrote in message
news:450900ad.0407151958.73494eeb@.posting.google.c om...
> I am extremely new at SQL Server2000 and t-sql and I'm looking to
> create a simple trigger. For explanation sake, let's say I have 3
> columns in one table ... Col_1, Col_2 and Col_3. The data type for
> Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on
> Col_2 to compare Col_1 to Col_2 when Col_2 is updated and if they're
> the same, set the value on Col_3 to "Completed". Can someone please
> help me?
> Thanks,
> Justin|||If col_3 is always based on the value of columns 1 and 2 then you don't need
it and you don't need the trigger either. Drop col_3 and calculate the
"completed" status in a view or when you query the table. Columns based on
other (non-key) columns are called Transitive Dependencies and avoiding them
is one of the goals of correct database design.

--
David Portas
SQL Server MVP
--|||David,

You're right, of course but I was just giving an example. What I
actually have is 19 columns. 18 of them are bit value 1 of them char.
The 18 represent 9 pairs. Each pair represents requested and created.
I wanted to write an update trigger on all 9 created fields that would
check to see if ALL of the requested fields were equal to thier
respective created fields and if they were, to Change the status of
field 19 from active to completed.

This is for a new user account form whereby a user would request access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
completed by the various administrators. That way every time one
administrator created their respective account the database would check
and possible change the status of the request.

If you have any insight in this area it would be much appreciated. I
certainly need all of the help I can get.

Thanks,

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi John,

Thanks for your help I really appreciate it. I tried your code and I
keep getting "invalid column name" error messages. Here is my attempt
at the code...

CREATE TRIGGER CHECKSTATUS
ON dbo.useraccounttbl
FOR UPDATE AS
IF UPDATE (internetcreated)
BEGIN
UPDATE t
SET completedstatus = "Completed"
FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE i.internetcreated <> d.internetccreated
AND i.internetcreated = i.internetrequested
END

I'm tring to test this on these 3 columns what I will need to do on 16
more. I explained it to another person who reponded like this...

What I actually have is 19 columns. 18 of them are bit value 1 of them
char. The 18 represent 9 pairs. Each pair represents requested and
created. I wanted to write an update trigger on all 9 created fields
that would check to see if ALL of the requested fields were equal to
thier respective created fields and if they were, to Change the status
of field 19 from active to completed.

This is for a new user account form whereby a user would request access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
created by the various administrators. That way every time one
administrator created their respective account the database would check
and possible change the status of the request.

Thanks again for all of your help.

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi

pkcol are the columns in the primary key, it could be more than one column
that uniquely defines each row. As there was/is no DDL it was used as short
hand.

You can pair each value

UPDATE t
SET completedstatus = "Completed"
FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE ( i.col1 <> d.col1
AND i.col1 = i.col2 )
OR ( i.col3 <> d.col3
AND i.col3 = i.col4 )
OR ( .......
)

John

"Justin Hosman" <hateface72@.yahoo.com> wrote in message
news:40f7dbfb$1$16414$c397aba@.news.newsgroups.ws.. .
> Hi John,
> Thanks for your help I really appreciate it. I tried your code and I
> keep getting "invalid column name" error messages. Here is my attempt
> at the code...
> CREATE TRIGGER CHECKSTATUS
> ON dbo.useraccounttbl
> FOR UPDATE AS
> IF UPDATE (internetcreated)
> BEGIN
> UPDATE t
> SET completedstatus = "Completed"
> FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
> JOIN DELETED d on d.pkcol = t.pkcol
> WHERE i.internetcreated <> d.internetccreated
> AND i.internetcreated = i.internetrequested
> END
> I'm tring to test this on these 3 columns what I will need to do on 16
> more. I explained it to another person who reponded like this...
> What I actually have is 19 columns. 18 of them are bit value 1 of them
> char. The 18 represent 9 pairs. Each pair represents requested and
> created. I wanted to write an update trigger on all 9 created fields
> that would check to see if ALL of the requested fields were equal to
> thier respective created fields and if they were, to Change the status
> of field 19 from active to completed.
> This is for a new user account form whereby a user would request access
> to certain systems (administrated in diffrent areas) and the request
> status would stay in an active state until all of the accounts were
> created by the various administrators. That way every time one
> administrator created their respective account the database would check
> and possible change the status of the request.
> Thanks again for all of your help.
> Justin
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||>> Here is my attempt at the code... <<

I am sure that someone will kludge something really ugly and complex
for you, but the real problem is that you are still an assembly level
programmer trying to move to an abstract high level language.

1) Stop putting that silly "-tbl" prefix on table names! This is
saying that your table models pieces of furniture that belong to user
accounts, according to ISO-11179. There is only one data structure in
SQL anyway. What you want is a name that tells us what set of
entities or relationship the table models; since it is a set,
something like "UserAccounts" is fine.

Volumns are not anything like Fields; until you know and think in
terms of rows and columns, you will never "get it" and will continue
to write BASIC in SQL.

2) SQL is a declarative language and probably the first such animal
you have ever seen. You tell it WHAT you want and it figures out HOW
to get it. This is a huge advance in programming. If the data
changes, the query stays the same but the execution plan changes with
you coding anything new.

>> What I actually have is 19 columns. 18 of them are bit value 1 of
them
char. The 18 represent 9 pairs. Each pair represents requested and
created. <<

ARRGH! Assembly language and punch card programming!! Think
relational, not physical. At best from this description, you have
nine attributes which ought to be in one column each; having "half an
atribute" is a design flaw called attribute splitting. At this point,
I have to guess at specs, but it looks like each attribute has two
values (requested and created). NEVER, NEVER, NEVER use BIT data (I
have an article due out soon in DBAzine with some of the many
reasons).

Sit down and design a status code with all the values you need; you
have
"requested" and "created", so nothing is needed for "failed" or
"rejected" in your situation?

The right way to do this is NOT with a trigger (procedural code!! The
HOW of the answer), but with a VIEW that will compute the status
(declarative code!! The WHAT of the answer).

>> This is for a new user account form whereby a user would request
access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
created by the various administrators. <<

Now we have specs and can see that you don't know about First Normal
Form (1NF). The nine pairs are a repeating group in the data model.
Let us "flatten" out the table.

CREATE TABLE UserAccounts
(user_id INTEGER NOT NULL -- tables relate to each other, files don't
REFERENCES Personnel (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
admin_area INTEGER NOT NULL -- tables relate to each other, files
don't
REFERENCES Administration (admin_area)
ON DELETE CASCADE
ON UPDATE CASCADE,
request_status INTEGER DEFAULT 0 NOT NULL -- zero is "requested,not
granted"
CHECK (request_status IN (..)) -- columns have
constraints,fields don't
PRIMARY KEY (user_id, admin_area));

Your report:

CREATE VIEW RequestStatus (user_id, total_status)
AS
SELECT user_id, MIN(request_status)
FROM UserAccounts
GROUP BY user_id;

Add or drop admin areas as you wish; add more status codes easily; DRI
actions maintain the schema for you. The schema is a WHOLE, not a
collection of files that stand alone -- design the whole!|||Justin Hosman (hateface72@.yahoo.com) writes:
> Thanks for your help I really appreciate it. I tried your code and I
> keep getting "invalid column name" error messages. Here is my attempt
> at the code...
> CREATE TRIGGER CHECKSTATUS
> ON dbo.useraccounttbl
> FOR UPDATE AS
> IF UPDATE (internetcreated)
> BEGIN
> UPDATE t
> SET completedstatus = "Completed"
> FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
> JOIN DELETED d on d.pkcol = t.pkcol
> WHERE i.internetcreated <> d.internetccreated
> AND i.internetcreated = i.internetrequested
> END

"Completed" should be 'Completed'. SQL Server actually permits you
to use both " and ' to delimit strings, but " is only possible under
certain conditions, so stick with '.

> What I actually have is 19 columns. 18 of them are bit value 1 of them
> char. The 18 represent 9 pairs. Each pair represents requested and
> created. I wanted to write an update trigger on all 9 created fields
> that would check to see if ALL of the requested fields were equal to
> thier respective created fields and if they were, to Change the status
> of field 19 from active to completed.

A computed column would be easier:

CREATE TABLE blablabla
(pkcol ...
request1 bit NOT NULL,
completed1 bit NOT NULL,
..
status AS (CASE WHEN request1 = completed1 AND
request2 = completed2 AND
...
request9 = comepleted9 THEN 'Completed'
ELSE 'Pending'
END)

However, this is not a very good design. You should probaly change
all these columns to rows, so that if a tenth request is added, all
you need to add one more row to defining table:

CREATE TABLE requesttypes
(reqtype char(3) NOT NULL, -- mnemonic
reqname varchar(40) NOT NULL, -- descriptive
CONSTRIAINT PK_reqtypes PRIMARY KEY requesttypes (reqtype))

CREATE TABLE requests (
usrid int NOT NULL,
reqtype char(3) NOT NULL,
status char(1) NOT NULL
-- New or completed
CONSTRAINT ckc_req_status CHECK (status IN ('N', 'C')),
CONSTRAINT pk_reqs PRIMARY KEY (usrid, reqtype),
CONSTRAINT fk_regtype FOREIGN KEY (reqtype)
REFERENCES requesttypes( requtype),
CONSTRAINR fk_userd FOREIGN KEY (usrid)
REFERENCES users (usrid))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 29, 2012

Creating an INSERT UPDATE Trigger that works on certain account

Creating an INSERT UPDATE Trigger that works on certain account

Creating an Audit trail on a table using a trigger

Sunday, March 25, 2012

Creating a Trigger which updates a linked server

Creating a trigger using a cursor

Creating a Trigger that emails when new record is added.

Creating a Trigger on Table Access

Creating a Trigger on Table Access

Creating a Trigger on Table Access

Creating a Trigger on Access

Creating a trigger on a table using a cursor.

Creating a trigger on a table using a cursor.

Creating a trigger for the row being updated

Creating a trigger for the row being updated

Creating A Trigger

Creating a Trigger

Creating a trigger

Wednesday, March 21, 2012

Creating a simple update trigger

Creating DB from scripts

Blog Archive

About Me