Is it possible fire an insert trigger in the context of a different user than
the user that does the insert.
In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
statement so security context other than the current user. However, in all
SQL Server versions, permission checking on objects referenced in the
trigger are bypassed as long as the ownership chain is unbroken. This means
that you only need to be concerned about the trigger security context if you
access objects owned by principals, use dynamic SQL or access objects
external to the current database.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
> Is it possible fire an insert trigger in the context of a different user
> than
> the user that does the insert.
|||We are using SQL2000. User insert table in one database T1 and trigger insert
the record into another database T2. In this scenario could you please be
more clear if I could use any user in T2 to be able to do this insertion.
Users in T1 should not be able to insert anything into T2.
"Dan Guzman" wrote:
> In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
> statement so security context other than the current user. However, in all
> SQL Server versions, permission checking on objects referenced in the
> trigger are bypassed as long as the ownership chain is unbroken. This means
> that you only need to be concerned about the trigger security context if you
> access objects owned by principals, use dynamic SQL or access objects
> external to the current database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
>
|||You might consider using cross-database ownership chaining to address your
security requirement. Assuming all objects are owned by dbo, you can do
this as follows:
1) Make sure both databases are owned by the same login. Execute
sp_changedbowner if necessary.
2) Make sure users are added to both databases so that they have a security
context. However, those users do not need object permissions in the second
database. Alternatively, you can enable the guest user in the second
database.
3) Enable the cross-database chaining ('db chaining' database option) for
both databases.
Note that you should understand the security implications of cross-database
chaining as described in the Books Online. Cross-database chaining should
be enabled in an sa-owned database when only sysadmin role members can
create dbo-owned objects in those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6EBDA8BD-9B96-4503-A1C8-95E8D872E34F@.microsoft.com...[vbcol=seagreen]
> We are using SQL2000. User insert table in one database T1 and trigger
> insert
> the record into another database T2. In this scenario could you please be
> more clear if I could use any user in T2 to be able to do this insertion.
> Users in T1 should not be able to insert anything into T2.
> "Dan Guzman" wrote:
Showing posts with label trigger. Show all posts
Showing posts with label trigger. Show all posts
Wednesday, March 7, 2012
q; user running trigger
Is it possible fire an insert trigger in the context of a different user tha
n
the user that does the insert.In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
statement so security context other than the current user. However, in all
SQL Server versions, permission checking on objects referenced in the
trigger are bypassed as long as the ownership chain is unbroken. This means
that you only need to be concerned about the trigger security context if you
access objects owned by principals, use dynamic SQL or access objects
external to the current database.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
> Is it possible fire an insert trigger in the context of a different user
> than
> the user that does the insert.|||We are using SQL2000. User insert table in one database T1 and trigger inser
t
the record into another database T2. In this scenario could you please be
more clear if I could use any user in T2 to be able to do this insertion.
Users in T1 should not be able to insert anything into T2.
"Dan Guzman" wrote:
> In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
> statement so security context other than the current user. However, in al
l
> SQL Server versions, permission checking on objects referenced in the
> trigger are bypassed as long as the ownership chain is unbroken. This mea
ns
> that you only need to be concerned about the trigger security context if y
ou
> access objects owned by principals, use dynamic SQL or access objects
> external to the current database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
>|||You might consider using cross-database ownership chaining to address your
security requirement. Assuming all objects are owned by dbo, you can do
this as follows:
1) Make sure both databases are owned by the same login. Execute
sp_changedbowner if necessary.
2) Make sure users are added to both databases so that they have a security
context. However, those users do not need object permissions in the second
database. Alternatively, you can enable the guest user in the second
database.
3) Enable the cross-database chaining ('db chaining' database option) for
both databases.
Note that you should understand the security implications of cross-database
chaining as described in the Books Online. Cross-database chaining should
be enabled in an sa-owned database when only sysadmin role members can
create dbo-owned objects in those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6EBDA8BD-9B96-4503-A1C8-95E8D872E34F@.microsoft.com...[vbcol=seagreen]
> We are using SQL2000. User insert table in one database T1 and trigger
> insert
> the record into another database T2. In this scenario could you please be
> more clear if I could use any user in T2 to be able to do this insertion.
> Users in T1 should not be able to insert anything into T2.
> "Dan Guzman" wrote:
>
n
the user that does the insert.In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
statement so security context other than the current user. However, in all
SQL Server versions, permission checking on objects referenced in the
trigger are bypassed as long as the ownership chain is unbroken. This means
that you only need to be concerned about the trigger security context if you
access objects owned by principals, use dynamic SQL or access objects
external to the current database.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
> Is it possible fire an insert trigger in the context of a different user
> than
> the user that does the insert.|||We are using SQL2000. User insert table in one database T1 and trigger inser
t
the record into another database T2. In this scenario could you please be
more clear if I could use any user in T2 to be able to do this insertion.
Users in T1 should not be able to insert anything into T2.
"Dan Guzman" wrote:
> In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
> statement so security context other than the current user. However, in al
l
> SQL Server versions, permission checking on objects referenced in the
> trigger are bypassed as long as the ownership chain is unbroken. This mea
ns
> that you only need to be concerned about the trigger security context if y
ou
> access objects owned by principals, use dynamic SQL or access objects
> external to the current database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
>|||You might consider using cross-database ownership chaining to address your
security requirement. Assuming all objects are owned by dbo, you can do
this as follows:
1) Make sure both databases are owned by the same login. Execute
sp_changedbowner if necessary.
2) Make sure users are added to both databases so that they have a security
context. However, those users do not need object permissions in the second
database. Alternatively, you can enable the guest user in the second
database.
3) Enable the cross-database chaining ('db chaining' database option) for
both databases.
Note that you should understand the security implications of cross-database
chaining as described in the Books Online. Cross-database chaining should
be enabled in an sa-owned database when only sysadmin role members can
create dbo-owned objects in those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6EBDA8BD-9B96-4503-A1C8-95E8D872E34F@.microsoft.com...[vbcol=seagreen]
> We are using SQL2000. User insert table in one database T1 and trigger
> insert
> the record into another database T2. In this scenario could you please be
> more clear if I could use any user in T2 to be able to do this insertion.
> Users in T1 should not be able to insert anything into T2.
> "Dan Guzman" wrote:
>
q; Transaction in trigger
Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?
A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>
|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?
A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>
|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
q; Transaction in trigger
Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transactio
n
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transactio
n
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
q; Transaction in trigger
Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
q; Running SP with a different user
We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possible
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?
insert the record into another database DB2. In this scenario, is it possible
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?
q; please check this trigger
Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,
Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>
|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,
Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>
|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
q; please check this trigger
Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
> > Apparently I could not insert a text field to another table from INSERTED in
> > a trigger.
> > It seems the following is working, do you see any problem joining INSERTED
> > to the mySrcTable which is the table that has this trigger
> >
> > INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> > SELECT i.myTrID, i.myFirstName, p.myBigText
> > FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> > WHERE (i.myTrType = 'In')
> >
> > Thanks,
> >
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
> > Apparently I could not insert a text field to another table from INSERTED in
> > a trigger.
> > It seems the following is working, do you see any problem joining INSERTED
> > to the mySrcTable which is the table that has this trigger
> >
> > INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> > SELECT i.myTrID, i.myFirstName, p.myBigText
> > FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> > WHERE (i.myTrType = 'In')
> >
> > Thanks,
> >
q; please check this trigger
Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the origina
l
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED
in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigT
ext)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if th
e
> compatibility level is equal to 70. The text, ntext, and image values in t
he
> inserted and deleted tables cannot be accessed. To retrieve the new value
in
> either an INSERT or UPDATE trigger, join the inserted table with the origi
nal
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update o
f
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
>
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the origina
l
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED
in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigT
ext)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if th
e
> compatibility level is equal to 70. The text, ntext, and image values in t
he
> inserted and deleted tables cannot be accessed. To retrieve the new value
in
> either an INSERT or UPDATE trigger, join the inserted table with the origi
nal
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update o
f
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
>
Saturday, February 25, 2012
q; INSRET/UPDATE trigger
Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there i
s
any workaround.
Thanks,Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:
> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case yo
u
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||On Fri, 29 Dec 2006 10:19:01 -0800, JIM.H. wrote:
>Hi,
>I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
>rollback record in the table?
Hi Jim,
It definitely does not. Run the following code in Sql Server Management
Studio or Query Analyzer to see for yourself:
CREATE TABLE Test
(a int NOT NULL PRIMARY KEY);
go
CREATE TRIGGER TestTrig
ON Test AFTER INSERT
AS
RAISERROR('This is a test', 16, 1);
go
INSERT INTO Test (a)
VALUES (1);
SELECT *
FROM Test;
go
DROP TABLE Test;
go
> It seems it does and I am wondering if there is
>any workaround.
Is it possible that your client automatically requests a rollback upon
seeing the error condition? I know some clients (Access, for instance)
that make a habit of rolling back first and asking questions later.
Hugo Kornelis, SQL Server MVP|||JIM.H. wrote:
> Hi Paul,
> Thanks you very much, no try check, here is the code:
> -- duplicate record check
> IF EXISTS (
> SELECT *
> FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
> WHERE i.tID=@.tID
> )
> Begin
> SET @.ErrorD = 'Dup Record'
> RAISERROR(@.ErrorD, 16, 1)
> Return
> End
>
>
Unfortunately you only posted part of the trigger code but this looks
suspiciously like your code is at fault. I suspect your trigger fails
to operate correctly when multiple rows are updated because you don't
appear to be referencing the INSERTED table properly. That might
explain some unpredictable behaviour.
If you want help to rewrite the trigger we'll need a better spec - read
my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there i
s
any workaround.
Thanks,Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:
> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case yo
u
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||On Fri, 29 Dec 2006 10:19:01 -0800, JIM.H. wrote:
>Hi,
>I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
>rollback record in the table?
Hi Jim,
It definitely does not. Run the following code in Sql Server Management
Studio or Query Analyzer to see for yourself:
CREATE TABLE Test
(a int NOT NULL PRIMARY KEY);
go
CREATE TRIGGER TestTrig
ON Test AFTER INSERT
AS
RAISERROR('This is a test', 16, 1);
go
INSERT INTO Test (a)
VALUES (1);
SELECT *
FROM Test;
go
DROP TABLE Test;
go
> It seems it does and I am wondering if there is
>any workaround.
Is it possible that your client automatically requests a rollback upon
seeing the error condition? I know some clients (Access, for instance)
that make a habit of rolling back first and asking questions later.
Hugo Kornelis, SQL Server MVP|||JIM.H. wrote:
> Hi Paul,
> Thanks you very much, no try check, here is the code:
> -- duplicate record check
> IF EXISTS (
> SELECT *
> FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
> WHERE i.tID=@.tID
> )
> Begin
> SET @.ErrorD = 'Dup Record'
> RAISERROR(@.ErrorD, 16, 1)
> Return
> End
>
>
Unfortunately you only posted part of the trigger code but this looks
suspiciously like your code is at fault. I suspect your trigger fails
to operate correctly when multiple rows are updated because you don't
appear to be referencing the INSERTED table properly. That might
explain some unpredictable behaviour.
If you want help to rewrite the trigger we'll need a better spec - read
my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
q; INSRET/UPDATE trigger
Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there is
any workaround.
Thanks,
Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:
> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case you
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there is
any workaround.
Thanks,
Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:
> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case you
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
q; how to write this trigger
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If thi
s is
a success, I need to set T1.Status=’Done’ for the updated records. So th
is
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=’Done’, any example would be greatly appreciated
.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:
>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>Done. If this i
s
>a success, I need to set T1.Status=Done for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=Done, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
..
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
Hugo Kornelis, SQL Server MVP
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If thi
s is
a success, I need to set T1.Status=’Done’ for the updated records. So th
is
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=’Done’, any example would be greatly appreciated
.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:
>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>Done. If this i
s
>a success, I need to set T1.Status=Done for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=Done, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
..
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
Hugo Kornelis, SQL Server MVP
q; how to write this trigger
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>â'Doneâ'. If this is
a success, I need to set T1.Status=â'Doneâ' for the updated records. So this
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=â'Doneâ', any example would be greatly appreciated.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:
>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>?Done?. If this is
>a success, I need to set T1.Status=?Done? for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=?Done?, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
...
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
--
Hugo Kornelis, SQL Server MVP
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>â'Doneâ'. If this is
a success, I need to set T1.Status=â'Doneâ' for the updated records. So this
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=â'Doneâ', any example would be greatly appreciated.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:
>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>?Done?. If this is
>a success, I need to set T1.Status=?Done? for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=?Done?, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
...
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
--
Hugo Kornelis, SQL Server MVP
q; duplicate dhcek on two columns
Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or not
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>
|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigger
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>
|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:
> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
>
>
|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>
|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sample
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value should
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>
> Hi Arnie,
> You're right - I missed that part of Jim's message.
> If this is indeed the requirement (Jim's post, as already pointed out by
> several others, is not entirely clear), he could use a trigger like
> this:
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> WHERE i.type = 'xyz'
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> Or, as an alternative, use an indexed view:
> CREATE VIEW dbo.MyView WITH SCHEMABINDING
> AS
> SELECT id -- more columns may be added to help other queries
> FROM dbo.<BaseTable>
> WHERE type = 'xyz';
> go
> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
> go
> (Untested)
> --
> Hugo Kornelis, SQL Server MVP
>
|||Or doens't like the responses.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...[vbcol=seagreen]
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or not
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>
|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigger
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>
|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:
> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
>
>
|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>
|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sample
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value should
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>
> Hi Arnie,
> You're right - I missed that part of Jim's message.
> If this is indeed the requirement (Jim's post, as already pointed out by
> several others, is not entirely clear), he could use a trigger like
> this:
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> WHERE i.type = 'xyz'
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> Or, as an alternative, use an indexed view:
> CREATE VIEW dbo.MyView WITH SCHEMABINDING
> AS
> SELECT id -- more columns may be added to help other queries
> FROM dbo.<BaseTable>
> WHERE type = 'xyz';
> go
> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
> go
> (Untested)
> --
> Hugo Kornelis, SQL Server MVP
>
|||Or doens't like the responses.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...[vbcol=seagreen]
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:
q; duplicate dhcek on two columns
Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or no
t
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-hand...#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigg
er
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or
not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.
>Adapted from http://www.sommarskog.se/error-hand...#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> )
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:
> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sampl
e
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value shou
ld
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
>|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
Hugo Kornelis, SQL Server MVP
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or no
t
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-hand...#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigg
er
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or
not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.
>Adapted from http://www.sommarskog.se/error-hand...#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> )
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:
> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sampl
e
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value shou
ld
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
>|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
Hugo Kornelis, SQL Server MVP
q; duplicate dhcek on two columns
Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=â'xyzâ' together are duplicated or not
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=â'abcâ' duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigger
> I need to check if T1.ID and T1.Type=â'xyzâ' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=â'abcâ' duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.
>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>)
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
--
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>>Hi
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>>Try the following code in your trigger:
>>IF EXISTS (SELECT *
>> FROM inserted i
>> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>>)
>>BEGIN
>> ROLLBACK TRANSACTION
>> RAISERROR('Duplicate Values Entered.', 16, 1)
>> RETURN
>>END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>>Hi
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>>Try the following code in your trigger:
>>IF EXISTS (SELECT *
>> FROM inserted i
>> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>>)
>>BEGIN
>> ROLLBACK TRANSACTION
>> RAISERROR('Duplicate Values Entered.', 16, 1)
>> RETURN
>>END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:
> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> > On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >
> >>Hi
> >>
> >>Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> >>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> >>
> >>Try the following code in your trigger:
> >>
> >>IF EXISTS (SELECT *
> >> FROM inserted i
> >> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> >>)
> >>BEGIN
> >> ROLLBACK TRANSACTION
> >> RAISERROR('Duplicate Values Entered.', 16, 1)
> >> RETURN
> >>END
> >
> > But this won't work. In an AFTER trigger, this will always result in
> > error since the new rows are already in the table once the trigger
> > fires. And in an INSTEAD OF trigger, this will catch the addition of a
> > second row if the first is already there, but it will miss the insertion
> > of two duplicate rows in a single INSERT execution.
> >
> > For an AFTER trigger, this would probably work (untested, though):
> >
> > IF EXISTS (SELECT *
> > FROM inserted AS i
> > INNER JOIN <BaseTable> AS b
> > ON i.id = b.id
> > AND i.type = b.type
> > GROUP BY b.id, b.type
> > HAVING COUNT(*) > 1)
> > BEGIN;
> > ROLLBACK TRANSACTION;
> > RAISERROR('Duplicate Values Entered.', 16, 1);
> > RETURN;
> > END;
> >
> > --
> > Hugo Kornelis, SQL Server MVP
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >Hi
> >
> >Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
> >Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> >
> >Try the following code in your trigger:
> >
> >IF EXISTS (SELECT *
> > FROM inserted i
> > JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> >)
> >BEGIN
> > ROLLBACK TRANSACTION
> > RAISERROR('Duplicate Values Entered.', 16, 1)
> > RETURN
> >END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sample
code.
John
"JIM.H." wrote:
> Thanks Hugo, unique constraints would not work since a specific value should
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
> > On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >
> > >Hi
> > >
> > >Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> > >Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> > >
> > >Try the following code in your trigger:
> > >
> > >IF EXISTS (SELECT *
> > > FROM inserted i
> > > JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> > >)
> > >BEGIN
> > > ROLLBACK TRANSACTION
> > > RAISERROR('Duplicate Values Entered.', 16, 1)
> > > RETURN
> > >END
> >
> > But this won't work. In an AFTER trigger, this will always result in
> > error since the new rows are already in the table once the trigger
> > fires. And in an INSTEAD OF trigger, this will catch the addition of a
> > second row if the first is already there, but it will miss the insertion
> > of two duplicate rows in a single INSERT execution.
> >
> > For an AFTER trigger, this would probably work (untested, though):
> >
> > IF EXISTS (SELECT *
> > FROM inserted AS i
> > INNER JOIN <BaseTable> AS b
> > ON i.id = b.id
> > AND i.type = b.type
> > GROUP BY b.id, b.type
> > HAVING COUNT(*) > 1)
> > BEGIN;
> > ROLLBACK TRANSACTION;
> > RAISERROR('Duplicate Values Entered.', 16, 1);
> > RETURN;
> > END;
> >
> > --
> > Hugo Kornelis, SQL Server MVP
> >|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
> >Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> >probably won't work for him.
> >
> >"check if T1.ID and T1.Type='xyz' together are duplicated"
> >AND
> >"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> Hi Arnie,
> You're right - I missed that part of Jim's message.
> If this is indeed the requirement (Jim's post, as already pointed out by
> several others, is not entirely clear), he could use a trigger like
> this:
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> WHERE i.type = 'xyz'
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> Or, as an alternative, use an indexed view:
> CREATE VIEW dbo.MyView WITH SCHEMABINDING
> AS
> SELECT id -- more columns may be added to help other queries
> FROM dbo.<BaseTable>
> WHERE type = 'xyz';
> go
> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
> go
> (Untested)
> --
> Hugo Kornelis, SQL Server MVP
>|||Or doens't like the responses.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:
>> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>> >Unless the OP has misstated his scenario, I think that a UNIQUE
>> >CONSTRAINT
>> >probably won't work for him.
>> >
>> >"check if T1.ID and T1.Type='xyz' together are duplicated"
>> >AND
>> >"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
>> Hi Arnie,
>> You're right - I missed that part of Jim's message.
>> If this is indeed the requirement (Jim's post, as already pointed out by
>> several others, is not entirely clear), he could use a trigger like
>> this:
>> IF EXISTS (SELECT *
>> FROM inserted AS i
>> INNER JOIN <BaseTable> AS b
>> ON i.id = b.id
>> AND i.type = b.type
>> WHERE i.type = 'xyz'
>> GROUP BY b.id, b.type
>> HAVING COUNT(*) > 1)
>> BEGIN;
>> ROLLBACK TRANSACTION;
>> RAISERROR('Duplicate Values Entered.', 16, 1);
>> RETURN;
>> END;
>> Or, as an alternative, use an indexed view:
>> CREATE VIEW dbo.MyView WITH SCHEMABINDING
>> AS
>> SELECT id -- more columns may be added to help other queries
>> FROM dbo.<BaseTable>
>> WHERE type = 'xyz';
>> go
>> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
>> go
>> (Untested)
>> --
>> Hugo Kornelis, SQL Server MVP
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=â'xyzâ' together are duplicated or not
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=â'abcâ' duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigger
> I need to check if T1.ID and T1.Type=â'xyzâ' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=â'abcâ' duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.
>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>)
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
--
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>>Hi
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>>Try the following code in your trigger:
>>IF EXISTS (SELECT *
>> FROM inserted i
>> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>>)
>>BEGIN
>> ROLLBACK TRANSACTION
>> RAISERROR('Duplicate Values Entered.', 16, 1)
>> RETURN
>>END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>>Hi
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>>Try the following code in your trigger:
>>IF EXISTS (SELECT *
>> FROM inserted i
>> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>>)
>>BEGIN
>> ROLLBACK TRANSACTION
>> RAISERROR('Duplicate Values Entered.', 16, 1)
>> RETURN
>>END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:
> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> > On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >
> >>Hi
> >>
> >>Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> >>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> >>
> >>Try the following code in your trigger:
> >>
> >>IF EXISTS (SELECT *
> >> FROM inserted i
> >> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> >>)
> >>BEGIN
> >> ROLLBACK TRANSACTION
> >> RAISERROR('Duplicate Values Entered.', 16, 1)
> >> RETURN
> >>END
> >
> > But this won't work. In an AFTER trigger, this will always result in
> > error since the new rows are already in the table once the trigger
> > fires. And in an INSTEAD OF trigger, this will catch the addition of a
> > second row if the first is already there, but it will miss the insertion
> > of two duplicate rows in a single INSERT execution.
> >
> > For an AFTER trigger, this would probably work (untested, though):
> >
> > IF EXISTS (SELECT *
> > FROM inserted AS i
> > INNER JOIN <BaseTable> AS b
> > ON i.id = b.id
> > AND i.type = b.type
> > GROUP BY b.id, b.type
> > HAVING COUNT(*) > 1)
> > BEGIN;
> > ROLLBACK TRANSACTION;
> > RAISERROR('Duplicate Values Entered.', 16, 1);
> > RETURN;
> > END;
> >
> > --
> > Hugo Kornelis, SQL Server MVP
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >Hi
> >
> >Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
> >Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> >
> >Try the following code in your trigger:
> >
> >IF EXISTS (SELECT *
> > FROM inserted i
> > JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> >)
> >BEGIN
> > ROLLBACK TRANSACTION
> > RAISERROR('Duplicate Values Entered.', 16, 1)
> > RETURN
> >END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sample
code.
John
"JIM.H." wrote:
> Thanks Hugo, unique constraints would not work since a specific value should
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
> > On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >
> > >Hi
> > >
> > >Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> > >Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> > >
> > >Try the following code in your trigger:
> > >
> > >IF EXISTS (SELECT *
> > > FROM inserted i
> > > JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> > >)
> > >BEGIN
> > > ROLLBACK TRANSACTION
> > > RAISERROR('Duplicate Values Entered.', 16, 1)
> > > RETURN
> > >END
> >
> > But this won't work. In an AFTER trigger, this will always result in
> > error since the new rows are already in the table once the trigger
> > fires. And in an INSTEAD OF trigger, this will catch the addition of a
> > second row if the first is already there, but it will miss the insertion
> > of two duplicate rows in a single INSERT execution.
> >
> > For an AFTER trigger, this would probably work (untested, though):
> >
> > IF EXISTS (SELECT *
> > FROM inserted AS i
> > INNER JOIN <BaseTable> AS b
> > ON i.id = b.id
> > AND i.type = b.type
> > GROUP BY b.id, b.type
> > HAVING COUNT(*) > 1)
> > BEGIN;
> > ROLLBACK TRANSACTION;
> > RAISERROR('Duplicate Values Entered.', 16, 1);
> > RETURN;
> > END;
> >
> > --
> > Hugo Kornelis, SQL Server MVP
> >|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
> >Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> >probably won't work for him.
> >
> >"check if T1.ID and T1.Type='xyz' together are duplicated"
> >AND
> >"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> Hi Arnie,
> You're right - I missed that part of Jim's message.
> If this is indeed the requirement (Jim's post, as already pointed out by
> several others, is not entirely clear), he could use a trigger like
> this:
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> WHERE i.type = 'xyz'
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> Or, as an alternative, use an indexed view:
> CREATE VIEW dbo.MyView WITH SCHEMABINDING
> AS
> SELECT id -- more columns may be added to help other queries
> FROM dbo.<BaseTable>
> WHERE type = 'xyz';
> go
> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
> go
> (Untested)
> --
> Hugo Kornelis, SQL Server MVP
>|||Or doens't like the responses.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:
>> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>> >Unless the OP has misstated his scenario, I think that a UNIQUE
>> >CONSTRAINT
>> >probably won't work for him.
>> >
>> >"check if T1.ID and T1.Type='xyz' together are duplicated"
>> >AND
>> >"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
>> Hi Arnie,
>> You're right - I missed that part of Jim's message.
>> If this is indeed the requirement (Jim's post, as already pointed out by
>> several others, is not entirely clear), he could use a trigger like
>> this:
>> IF EXISTS (SELECT *
>> FROM inserted AS i
>> INNER JOIN <BaseTable> AS b
>> ON i.id = b.id
>> AND i.type = b.type
>> WHERE i.type = 'xyz'
>> GROUP BY b.id, b.type
>> HAVING COUNT(*) > 1)
>> BEGIN;
>> ROLLBACK TRANSACTION;
>> RAISERROR('Duplicate Values Entered.', 16, 1);
>> RETURN;
>> END;
>> Or, as an alternative, use an indexed view:
>> CREATE VIEW dbo.MyView WITH SCHEMABINDING
>> AS
>> SELECT id -- more columns may be added to help other queries
>> FROM dbo.<BaseTable>
>> WHERE type = 'xyz';
>> go
>> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
>> go
>> (Untested)
>> --
>> Hugo Kornelis, SQL Server MVP
q; a trigger code
I have table T1 and I am trying to extract some date from T1 table based on
inserts, updates and deletes. The destination table T2 has three fields F1,
F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my
first trigger, Can anyone write this trigger for me?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields
> F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:
> I would NEVER write any code that had table names like T1 or T2, and columns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields
> > F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
>
>|||JIM.H. wrote:
> I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>> I would NEVER write any code that had table names like T1 or T2, and
>> columns
>> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
>> that you can post?
>> Give us more to work with, and you may get some good assistance here.
>> For example, the the code snipplet, is TYPE a column name, a literal, a
>> varable? Where did it come from?
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >I have table T1 and I am trying to extract some date from T1 table based
>> >on
>> > inserts, updates and deletes. The destination table T2 has three fields
>> > F1,
>> > F2, F3, so I need something like
>> >
>> > Insert into T2 (F1, F2, F3)
>> > Select (F1, F2,Type)
>> > From T1
>> >
>> > Type should be defined based on Insert, Update, or Delete. This will be
>> > my
>> > first trigger, Can anyone write this trigger for me?
>> >
>> >
>> >
>> >
>>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:
> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' have
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> > Hi Arnie,
> >
> > That was just to simplify the case. I do not have actual table definitions
> > yet. Type is what I am trying to determine in the trigger. This will be
> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
> >
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> I would NEVER write any code that had table names like T1 or T2, and
> >> columns
> >> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> >> that you can post?
> >>
> >> Give us more to work with, and you may get some good assistance here.
> >>
> >> For example, the the code snipplet, is TYPE a column name, a literal, a
> >> varable? Where did it come from?
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >> >I have table T1 and I am trying to extract some date from T1 table based
> >> >on
> >> > inserts, updates and deletes. The destination table T2 has three fields
> >> > F1,
> >> > F2, F3, so I need something like
> >> >
> >> > Insert into T2 (F1, F2, F3)
> >> > Select (F1, F2,Type)
> >> > From T1
> >> >
> >> > Type should be defined based on Insert, Update, or Delete. This will be
> >> > my
> >> > first trigger, Can anyone write this trigger for me?
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted]. They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] will be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>> Yes, inside the Trigger you will be able to determine if it is executing
>> because of INSERT, UPDATE, DELETE.
>> If you are exploring some form of auditing, the archive table 'should'
>> have
>> additional columns for the user and current date/time. Those are also
>> available internal to the Trigger.
>> Let us know how to help you when you're closer to the need.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>> > Hi Arnie,
>> >
>> > That was just to simplify the case. I do not have actual table
>> > definitions
>> > yet. Type is what I am trying to determine in the trigger. This will be
>> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the
>> > trigger?
>> >
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> I would NEVER write any code that had table names like T1 or T2, and
>> >> columns
>> >> named F1, F2, F3. Is this real (and I hope not), or do you have table
>> >> DDL
>> >> that you can post?
>> >>
>> >> Give us more to work with, and you may get some good assistance here.
>> >>
>> >> For example, the the code snipplet, is TYPE a column name, a literal,
>> >> a
>> >> varable? Where did it come from?
>> >>
>> >> --
>> >> Arnie Rowland*
>> >> "To be successful, your heart must accompany your knowledge."
>> >>
>> >>
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >> >I have table T1 and I am trying to extract some date from T1 table
>> >> >based
>> >> >on
>> >> > inserts, updates and deletes. The destination table T2 has three
>> >> > fields
>> >> > F1,
>> >> > F2, F3, so I need something like
>> >> >
>> >> > Insert into T2 (F1, F2, F3)
>> >> > Select (F1, F2,Type)
>> >> > From T1
>> >> >
>> >> > Type should be defined based on Insert, Update, or Delete. This will
>> >> > be
>> >> > my
>> >> > first trigger, Can anyone write this trigger for me?
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for me:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
inserts, updates and deletes. The destination table T2 has three fields F1,
F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my
first trigger, Can anyone write this trigger for me?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields
> F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:
> I would NEVER write any code that had table names like T1 or T2, and columns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields
> > F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
>
>|||JIM.H. wrote:
> I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>> I would NEVER write any code that had table names like T1 or T2, and
>> columns
>> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
>> that you can post?
>> Give us more to work with, and you may get some good assistance here.
>> For example, the the code snipplet, is TYPE a column name, a literal, a
>> varable? Where did it come from?
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >I have table T1 and I am trying to extract some date from T1 table based
>> >on
>> > inserts, updates and deletes. The destination table T2 has three fields
>> > F1,
>> > F2, F3, so I need something like
>> >
>> > Insert into T2 (F1, F2, F3)
>> > Select (F1, F2,Type)
>> > From T1
>> >
>> > Type should be defined based on Insert, Update, or Delete. This will be
>> > my
>> > first trigger, Can anyone write this trigger for me?
>> >
>> >
>> >
>> >
>>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:
> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' have
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> > Hi Arnie,
> >
> > That was just to simplify the case. I do not have actual table definitions
> > yet. Type is what I am trying to determine in the trigger. This will be
> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
> >
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> I would NEVER write any code that had table names like T1 or T2, and
> >> columns
> >> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> >> that you can post?
> >>
> >> Give us more to work with, and you may get some good assistance here.
> >>
> >> For example, the the code snipplet, is TYPE a column name, a literal, a
> >> varable? Where did it come from?
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >> >I have table T1 and I am trying to extract some date from T1 table based
> >> >on
> >> > inserts, updates and deletes. The destination table T2 has three fields
> >> > F1,
> >> > F2, F3, so I need something like
> >> >
> >> > Insert into T2 (F1, F2, F3)
> >> > Select (F1, F2,Type)
> >> > From T1
> >> >
> >> > Type should be defined based on Insert, Update, or Delete. This will be
> >> > my
> >> > first trigger, Can anyone write this trigger for me?
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted]. They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] will be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>> Yes, inside the Trigger you will be able to determine if it is executing
>> because of INSERT, UPDATE, DELETE.
>> If you are exploring some form of auditing, the archive table 'should'
>> have
>> additional columns for the user and current date/time. Those are also
>> available internal to the Trigger.
>> Let us know how to help you when you're closer to the need.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>> > Hi Arnie,
>> >
>> > That was just to simplify the case. I do not have actual table
>> > definitions
>> > yet. Type is what I am trying to determine in the trigger. This will be
>> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the
>> > trigger?
>> >
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> I would NEVER write any code that had table names like T1 or T2, and
>> >> columns
>> >> named F1, F2, F3. Is this real (and I hope not), or do you have table
>> >> DDL
>> >> that you can post?
>> >>
>> >> Give us more to work with, and you may get some good assistance here.
>> >>
>> >> For example, the the code snipplet, is TYPE a column name, a literal,
>> >> a
>> >> varable? Where did it come from?
>> >>
>> >> --
>> >> Arnie Rowland*
>> >> "To be successful, your heart must accompany your knowledge."
>> >>
>> >>
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >> >I have table T1 and I am trying to extract some date from T1 table
>> >> >based
>> >> >on
>> >> > inserts, updates and deletes. The destination table T2 has three
>> >> > fields
>> >> > F1,
>> >> > F2, F3, so I need something like
>> >> >
>> >> > Insert into T2 (F1, F2, F3)
>> >> > Select (F1, F2,Type)
>> >> > From T1
>> >> >
>> >> > Type should be defined based on Insert, Update, or Delete. This will
>> >> > be
>> >> > my
>> >> > first trigger, Can anyone write this trigger for me?
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for me:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
q; a trigger code
I have table T1 and I am trying to extract some date from T1 table based on
inserts, updates and deletes. The destination table T2 has three fields F1,
F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my
first trigger, Can anyone write this trigger for me?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields
> F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:
> I would NEVER write any code that had table names like T1 or T2, and colum
ns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>
>|||JIM.H. wrote:
> I have table T1 and I am trying to extract some date from T1 table based o
n
> inserts, updates and deletes. The destination table T2 has three fields F1
,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...[vbcol=seagreen]
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:
> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' hav
e
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted].
They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] wil
l be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will
be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...[vbcol=seagreen]
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for m
e:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
inserts, updates and deletes. The destination table T2 has three fields F1,
F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my
first trigger, Can anyone write this trigger for me?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields
> F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:
> I would NEVER write any code that had table names like T1 or T2, and colum
ns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>
>|||JIM.H. wrote:
> I have table T1 and I am trying to extract some date from T1 table based o
n
> inserts, updates and deletes. The destination table T2 has three fields F1
,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...[vbcol=seagreen]
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:
> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' hav
e
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted].
They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] wil
l be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will
be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...[vbcol=seagreen]
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for m
e:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
q:INSRET/UPDATE trigger
Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this rollback record in the table? It seems it does and I am wondering if there is any workaround.
Thanks,
No, that shouldn't rollback INSERT/UPDATE to the table, unless you define the trigger as "INSTEAD OF" trigger. You can take a look atCREATE TRIGGERMonday, February 20, 2012
Q: trigger and notification
Hello,
How would you create a trigger on a table modification/delete/add and send
an intication do an e-mail address?
Thanks,
JimHi Jim,
I guess you are looking at something like this...
CREATE TRIGGER tr_test
ON yourtable
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'email address',
'Data inserted, modified or deleted from yourtable'
Also look at BOL for "CREATE TRIGGER"
Thanks
Yogish|||I get this error message after calling stored procedure.
xp_sendmail: Either there is no default mail client or the current mail
client cannot fulfill the messaging request. Please run Microsoft Outlook an
d
set it as the default mail client.
"Yogish" wrote:
> Hi Jim,
> I guess you are looking at something like this...
> CREATE TRIGGER tr_test
> ON yourtable
> FOR INSERT, UPDATE, DELETE
> AS
> EXEC master..xp_sendmail 'email address',
> 'Data inserted, modified or deleted from yourtable'
> Also look at BOL for "CREATE TRIGGER"
> --
> Thanks
> Yogish
>|||You need to configure SQL Mail. You can find the steps
outlined in the following article:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
-Sue
On Wed, 12 Jan 2005 13:41:06 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I get this error message after calling stored procedure.
>xp_sendmail: Either there is no default mail client or the current mail
>client cannot fulfill the messaging request. Please run Microsoft Outlook a
nd
>set it as the default mail client.
>"Yogish" wrote:
>
How would you create a trigger on a table modification/delete/add and send
an intication do an e-mail address?
Thanks,
JimHi Jim,
I guess you are looking at something like this...
CREATE TRIGGER tr_test
ON yourtable
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'email address',
'Data inserted, modified or deleted from yourtable'
Also look at BOL for "CREATE TRIGGER"
Thanks
Yogish|||I get this error message after calling stored procedure.
xp_sendmail: Either there is no default mail client or the current mail
client cannot fulfill the messaging request. Please run Microsoft Outlook an
d
set it as the default mail client.
"Yogish" wrote:
> Hi Jim,
> I guess you are looking at something like this...
> CREATE TRIGGER tr_test
> ON yourtable
> FOR INSERT, UPDATE, DELETE
> AS
> EXEC master..xp_sendmail 'email address',
> 'Data inserted, modified or deleted from yourtable'
> Also look at BOL for "CREATE TRIGGER"
> --
> Thanks
> Yogish
>|||You need to configure SQL Mail. You can find the steps
outlined in the following article:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
-Sue
On Wed, 12 Jan 2005 13:41:06 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I get this error message after calling stored procedure.
>xp_sendmail: Either there is no default mail client or the current mail
>client cannot fulfill the messaging request. Please run Microsoft Outlook a
nd
>set it as the default mail client.
>"Yogish" wrote:
>
Subscribe to:
Posts (Atom)