Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2012-06-20 : 21:07:48
|
Can anyone assist with my trigger? I basically have an insert script that I want to automate with a trigger, but I cannot get the trigger to work? - any help greatly appreciated.CREATE TRIGGER [dbo].[Trigger2] ON [dbo].[contacts]insert into Opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated, contacts.dchanged, contacts.dchanged from contacts where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 ) |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-20 : 21:36:25
|
your trigger doesnt make much sense. As of now it inserts entire records from contacts sstisfying where condition each time you do a DML operation (i dont know what operation as you've not specified it in posted code above. it should be either insert,update or delete).Also I think you should be using temporary tables inserted,deleted inside trigger to capture only rows affected by DML operation------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2012-06-20 : 21:41:52
|
I managed to figure it outUSE Webinar_JJ;GOIF OBJECT_ID ('Trigger4','TR') IS NOT NULL DROP TRIGGER Trigger4;GO-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table-- when the credit rating of the specified vendor is set to 5 (below average).CREATE TRIGGER Trigger4 ON ContactsAFTER UPDATEASIF EXISTS (select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated, contacts.dchanged, contacts.dchanged from contacts (nolock)where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 ) )BEGINRETURN END;GO-- This statement attempts to insert a row into the PurchaseOrderHeader table-- for a vendor that has a below average credit rating.-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.insert into Opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated, contacts.dchanged, contacts.dchanged from contacts where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 );GO |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-20 : 21:45:04
|
sorry still this looks a bit odd!...IF EXISTS (select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated, contacts.dchanged, contacts.dchanged from contacts (nolock)where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 )) as it still looks for entire table not records that are affected by update alone------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2012-06-20 : 22:27:31
|
Hi there, I was hoping my SQL trigger would see a record change and then insert a record into a new table, but as you can see it is not exactly working.Any help appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-20 : 22:44:56
|
can you first explain your full scenario? for each update, what exactly you want to trigger to do?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2012-06-20 : 23:05:18
|
Sure, the trigger concept is quite simple.When the contacts.iyesno1 field changes from 0 to 1 then I want to extract some of that data from the contacts table and insert it into the opt_out table.Everything works except I have to keep running the trigger. Instead I want the trigger to sit there, see the change and then act. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-20 : 23:09:52
|
As per your explanation below is sufficientUSE Webinar_JJ;GOIF OBJECT_ID ('Trigger4','TR') IS NOT NULLDROP TRIGGER Trigger4;GO-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table-- when the credit rating of the specified vendor is set to 5 (below average).CREATE TRIGGER Trigger4 ON ContactsAFTER UPDATEASBEGININSERT INTO opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)select i.contactid, i.cfirstname, i.clastname, i.cemail1, i.cphone1, i.dcreated, i.dchanged, i.dchanged from inserted iinner join deleted don i.contactid = d.contactidwhere i.iyesno1 = '1' and d.iyesno1 = '0'ENDGO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|