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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 My First Trigger and of course it blows up.

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-06 : 10:25:09
First off, I know this trigger isn't practical for anything, I am simply taking small steps towards the trigger I want.

My ERP database has a table called SORels (sales order releases) and I want to create a trigger to fire on it when records are added, updated, or deleted. I decided to start with updating. I created another database to keep track of the data called SOModifications with a table called soModTest.

The Create statement for soModTest is: 

CREATE TABLE [soModTest] (
	[fenumber] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fsono] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fnetprice] [numeric](17, 5) NOT NULL ,
	[forderqty] [numeric](15, 5) NOT NULL ,
	[funetprice] [numeric](17, 5) NOT NULL ,
	[identity_column] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO


The Trigger is here: 

CREATE TRIGGER SoModAudit 
ON M2MData01.dbo.Sorels
AFTER UPDATE
AS
SET NOCOUNT ON
INSERT SOModifications.dbo.soModTest(fenumber, fsono, fnetprice, forderqty, funetprice, identity_column)
VALUES('999','123456', 100.95000, 1.00000, 100.95000  ,987654)


Go


Can anyone tell me why I am getting a "table update failure on SORELS! Check Table" error when I try to update it through my ERP software?

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2008-08-06 : 10:44:50
You're trying to insert 987654 into an identity column, which is a no-no. The idenity column is automatic so you can't insert a specific value in to it

Jim
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-06 : 11:09:40
So, should it read like this? 

CREATE TRIGGER SoModAudit 
ON M2MData01.dbo.Sorels
AFTER UPDATE
AS
SET NOCOUNT ON
INSERT SOModifications.dbo.soModTest(fenumber, fsono, fnetprice, forderqty, funetprice)
VALUES('999','123456', 100.95000, 1.00000, 100.95000)


Go
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2008-08-06 : 11:19:40
Yes, and the identity column will automatically increase by 1. If you specifically need to put the 987654 in, then you'd have to change the data type in the table

Jim
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-06 : 11:26:26
Thanks Jim, that worked.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-06 : 16:06:15
So, here's my current status of the code: 

CREATE TRIGGER SOMODAUDIT
ON M2MDATA01.DBO.SORELS
AFTER UPDATE,INSERT
AS
  SET NOCOUNT ON
  
  INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYOLD,
         FORDERQTYNEW,
         FUNETPRICEOLD,
         FUNETPRICENEW,
         PRICEMOD,
         MODTYPE)
  SELECT I.FENUMBER,
         I.FSONO,
         I.FRELEASE,
         GETDATE(),
         D.FORDERQTY,
         I.FORDERQTY,
         D.FUNETPRICE,
         I.FUNETPRICE,
         (I.FNETPRICE - D.FNETPRICE) AS PRICEMOD,
         'C'
  FROM   INSERTED I
         INNER JOIN DELETED D
           ON I.IDENTITY_COLUMN = D.IDENTITY_COLUMN
  WHERE  I.FNETPRICE <> D.FNETPRICE
          OR I.FORDERQTY <> D.FORDERQTY
                            
GO


This does a good job for updates. However, I also need this to work for Inserts and Deletes. I know this can be done on 1 trigger, but I'm having trouble understanding how. Ideally for an insert you would insert something in the new table like:

002, 281345, 000, Date, NULL, 50.00000, NULL, 123.00000, 6150.00000, 'N'

And a Delete would look like this:

002, 281345, 000, Date, 50.00000, NULL, 123.00000, NULL, -6150.00000, 'D'

A nudge in the right direction would be wonderful.
Go to Top of Page

jrussell
Starting Member

1 Post
Posted - 2008-08-06 : 17:47:16
Try this in your trigger ...

IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
-- Code for UPDATE
ELSE
IF EXISTS(SELECT * FROM inserted)
-- Code for INSERT
ELSE
-- Code for DELETE
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts
Posted - 2008-08-06 : 19:22:13
that trigger is hardly blowing up.

what you need to do is write a CLR trigger and call Environment.Exit() from it!

elsasoft.org
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-07 : 02:19:54
[code]CREATE TRIGGER SOMODAUDIT
ON M2MDATA01.DBO.SORELS
AFTER UPDATE,INSERT,DELETE
AS
SET NOCOUNT ON

INSERT SOMODIFICATIONS.DBO.SOMODTEST
(FENUMBER,
FSONO,
FRELEASE,
FDATECHANGED,
FORDERQTYOLD,
FORDERQTYNEW,
FUNETPRICEOLD,
FUNETPRICENEW,
PRICEMOD,
MODTYPE)
SELECT COALESCE(I.FENUMBER,D.FENUMBER),
COALESCE(I.FSONO,D.FSONO),
COALESCE(I.FRELEASE,D.FRELEASE),
GETDATE(),
D.FORDERQTY,
I.FORDERQTY,
D.FUNETPRICE,
I.FUNETPRICE,
(COALESCE(I.FNETPRICE,0) - COALESCE(D.FNETPRICE,0)) AS PRICEMOD,
CASE WHEN I.IDENTITY_COLUMN IS NOT NULL AND D.IDENTITY_COLUMN IS NULL
THEN 'N'
WHEN I.IDENTITY_COLUMN IS NULL AND D.IDENTITY_COLUMN IS NOT NULL
THEN 'D'
ELSE 'C'
END
FROM INSERTED I
LEFT JOIN DELETED D
ON I.IDENTITY_COLUMN = D.IDENTITY_COLUMN
WHERE I.FNETPRICE <> D.FNETPRICE
OR I.FORDERQTY <> D.FORDERQTY

GO[/code]
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-07 : 09:58:38
quote:
Originally posted by jrussell

Try this in your trigger ...

IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
-- Code for UPDATE
ELSE
IF EXISTS(SELECT * FROM inserted)
-- Code for INSERT
ELSE
-- Code for DELETE




Thanks for the advice jrussell, before I saw this, I actually did something just like that. 


CREATE TRIGGER SOMODAUDIT
ON M2MDATA01.DBO.SORELS
AFTER UPDATE,INSERT, DELETE
AS
  SET NOCOUNT ON

if exists(select * from inserted) and exists(select * from deleted) -- Update
Begin
  INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYOLD,
         FORDERQTYNEW,
         FUNETPRICEOLD,
         FUNETPRICENEW,
         PRICEMOD,
         MODTYPE)
  SELECT I.FENUMBER,
         I.FSONO,
         I.FRELEASE,
         GETDATE(),
         D.FORDERQTY,
         I.FORDERQTY,
         D.FUNETPRICE,
         I.FUNETPRICE,
         (I.FNETPRICE - D.FNETPRICE) AS PRICEMOD,
         'C'
  FROM   INSERTED I
         INNER JOIN DELETED D
           ON I.IDENTITY_COLUMN = D.IDENTITY_COLUMN
  WHERE  I.FNETPRICE <> D.FNETPRICE
          OR I.FORDERQTY <> D.FORDERQTY
	Return
End 
if not exists(select * from inserted) and exists(select * from deleted) -- Delete
	Begin
  INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYOLD,
         FUNETPRICEOLD,
         PRICEMOD,
         MODTYPE)
  SELECT D.FENUMBER,
         D.FSONO,
         D.FRELEASE,
         GETDATE(),
         D.FORDERQTY,
         D.FUNETPRICE,
         (0 - D.FNETPRICE) AS PRICEMOD,
         'D'
  FROM   DELETED D

	Return		
	End

if exists(select * from inserted) and not exists(select * from deleted) -- Insert
Begin
 INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYNEW,
         FUNETPRICENEW,
         PRICEMOD,
         MODTYPE)
  SELECT I.FENUMBER,
         I.FSONO,
         I.FRELEASE,
         GETDATE(),
         I.FORDERQTY,
         I.FUNETPRICE,
         (I.FNETPRICE) AS PRICEMOD,
         'N'
  FROM   INSERTED I

	Return    
END                     
GO
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-07 : 10:00:29
Thanks again Visakh. I was kind of proud of what I came up with, and then I see your response and got just another small reminder of how little I know about this stuff.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-07 : 14:50:51
quote:
Originally posted by visakh16

CREATE TRIGGER SOMODAUDIT
ON M2MDATA01.DBO.SORELS
AFTER UPDATE,INSERT,DELETE
AS
  SET NOCOUNT ON
  
  INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYOLD,
         FORDERQTYNEW,
         FUNETPRICEOLD,
         FUNETPRICENEW,
         PRICEMOD,
         MODTYPE)
  SELECT COALESCE(I.FENUMBER,D.FENUMBER),
         COALESCE(I.FSONO,D.FSONO),
         COALESCE(I.FRELEASE,D.FRELEASE),
         GETDATE(),
         D.FORDERQTY,
         I.FORDERQTY,
         D.FUNETPRICE,
         I.FUNETPRICE,
         (COALESCE(I.FNETPRICE,0) - COALESCE(D.FNETPRICE,0)) AS PRICEMOD,
         CASE WHEN I.IDENTITY_COLUMN  IS NOT NULL AND D.IDENTITY_COLUMN  IS NULL
              THEN 'N'
              WHEN I.IDENTITY_COLUMN  IS NULL AND D.IDENTITY_COLUMN  IS NOT NULL
              THEN 'D'
              ELSE 'C'
              END
  FROM   INSERTED I
         LEFT JOIN DELETED D
           ON I.IDENTITY_COLUMN = D.IDENTITY_COLUMN
  WHERE  I.FNETPRICE <> D.FNETPRICE
          OR I.FORDERQTY <> D.FORDERQTY
                            
GO



I appreciate your help as always Visahk, but that doesn't work. I don't think it will capture the deletes because of the left join.

My verbose 3 tiered statement works. Can I implement a trigger while people are actively using the database? If I want to remove it, can I do it with people in?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-07 : 14:52:31
quote:
Originally posted by DavidChel

quote:
Originally posted by visakh16

CREATE TRIGGER SOMODAUDIT
ON M2MDATA01.DBO.SORELS
AFTER UPDATE,INSERT,DELETE
AS
  SET NOCOUNT ON
  
  INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYOLD,
         FORDERQTYNEW,
         FUNETPRICEOLD,
         FUNETPRICENEW,
         PRICEMOD,
         MODTYPE)
  SELECT COALESCE(I.FENUMBER,D.FENUMBER),
         COALESCE(I.FSONO,D.FSONO),
         COALESCE(I.FRELEASE,D.FRELEASE),
         GETDATE(),
         D.FORDERQTY,
         I.FORDERQTY,
         D.FUNETPRICE,
         I.FUNETPRICE,
         (COALESCE(I.FNETPRICE,0) - COALESCE(D.FNETPRICE,0)) AS PRICEMOD,
         CASE WHEN I.IDENTITY_COLUMN  IS NOT NULL AND D.IDENTITY_COLUMN  IS NULL
              THEN 'N'
              WHEN I.IDENTITY_COLUMN  IS NULL AND D.IDENTITY_COLUMN  IS NOT NULL
              THEN 'D'
              ELSE 'C'
              END
  FROM   INSERTED I
         FULL OUTER JOIN DELETED D
           ON I.IDENTITY_COLUMN = D.IDENTITY_COLUMN
  WHERE  I.FNETPRICE <> D.FNETPRICE
          OR I.FORDERQTY <> D.FORDERQTY
                            
GO



I appreciate your help as always Visahk, but that doesn't work. I don't think it will capture the deletes because of the left join.

My verbose 3 tiered statement works. Can I implement a trigger while people are actively using the database? If I want to remove it, can I do it with people in?



ah....make it full outer
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-07 : 14:54:12
Yup...you can create a trigger but this will surely have a serious impact on your system performance especially with lots of users interacting.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-07 : 14:55:22
I doubt it would be a problem here. But my second question stands, can I remove it without exclusive access to the database if I need it?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-11 : 08:42:51
quote:
Originally posted by DavidChel

I doubt it would be a problem here. But my second question stands, can I remove it without exclusive access to the database if I need it?



Anybody?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-11 : 10:21:26
quote:
Originally posted by DavidChel

I doubt it would be a problem here. But my second question stands, can I remove it without exclusive access to the database if I need it?


remove the trigger you mean? for that you need the alter table permission on table on which trigger is created.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-11 : 10:53:48
[code]CREATE TRIGGER SOMODAUDIT
ON M2MDATA01.DBO.SORELS
AFTER UPDATE,
INSERT,
DELETE
AS

SET NOCOUNT ON

DECLARE @Action CHAR(1)

IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SET @Action = 'U' -- Update
ELSE
SET @Action = 'I' -- Insert
ELSE
SET @Action = 'D' -- Delete

INSERT SOMODIFICATIONS.DBO.SOMODTEST
(
FENUMBER,
FSONO,
FRELEASE,
FDATECHANGED,
FORDERQTYOLD,
FORDERQTYNEW,
FUNETPRICEOLD,
FUNETPRICENEW,
PRICEMOD,
MODTYPE
)
SELECT i.FENUMBER,
i.FSONO,
i.FRELEASE,
GETDATE(),
d.FORDERQTY,
i.FORDERQTY,
d.FUNETPRICE,
i.FUNETPRICE,
(i.FNETPRICE - d.FNETPRICE),
@Action
FROM inserted AS i
FULL JOIN deleted AS d ON d.IDENTITY_COLUMN = i.IDENTITY_COLUMN[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-11 : 11:30:08
quote:
Originally posted by visakh16

quote:
Originally posted by DavidChel

I doubt it would be a problem here. But my second question stands, can I remove it without exclusive access to the database if I need it?


remove the trigger you mean? for that you need the alter table permission on table on which trigger is created.


I have full permissions. If I decided that the trigger wasn't working out or was having a deleterious effect on performance, could I remove it while users were still actively using the database?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-11 : 14:39:14
While I greatly appreciate everyone's help, this is the only one that seems to work (which was from a few days ago): 

CREATE TRIGGER SOMODAUDIT
ON M2MDATA01.DBO.SORELS
AFTER UPDATE,INSERT, DELETE
AS
  SET NOCOUNT ON

if exists(select * from inserted) and exists(select * from deleted) -- Update
Begin
  INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYOLD,
         FORDERQTYNEW,
         FUNETPRICEOLD,
         FUNETPRICENEW,
         FDISCPCTOLD,
         FDISCPCTNEW,
         PRICEMOD,
         MODTYPE)
  SELECT I.FENUMBER,
         I.FSONO,
         I.FRELEASE,
         GETDATE(),
         D.FORDERQTY,
         I.FORDERQTY,
         D.FUNETPRICE,
         I.FUNETPRICE,
         D.FDISCPCT,
         I.FDISCPCT,
         (I.FNETPRICE - D.FNETPRICE) AS PRICEMOD,
         'C'
  FROM   INSERTED I
         INNER JOIN DELETED D
           ON I.IDENTITY_COLUMN = D.IDENTITY_COLUMN
  WHERE  I.FNETPRICE <> D.FNETPRICE
          OR I.FORDERQTY <> D.FORDERQTY
	Return
End 
if not exists(select * from inserted) and exists(select * from deleted) -- Delete
	Begin
  INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYOLD,
         FUNETPRICEOLD,
         PRICEMOD,
         MODTYPE)
  SELECT D.FENUMBER,
         D.FSONO,
         D.FRELEASE,
         GETDATE(),
         D.FORDERQTY,
         D.FUNETPRICE,
         (0 - D.FNETPRICE) AS PRICEMOD,
         'D'
  FROM   DELETED D

	Return		
	End

if exists(select * from inserted) and not exists(select * from deleted) -- Insert
Begin
 INSERT SOMODIFICATIONS.DBO.SOMODTEST
        (FENUMBER,
         FSONO,
         FRELEASE,
         FDATECHANGED,
         FORDERQTYNEW,
         FUNETPRICENEW,
         PRICEMOD,
         MODTYPE)
  SELECT I.FENUMBER,
         I.FSONO,
         I.FRELEASE,
         GETDATE(),
         I.FORDERQTY,
         I.FUNETPRICE,
         (I.FNETPRICE) AS PRICEMOD,
         'N'
  FROM   INSERTED I

	Return    
END                     
GO


The bolded code needs to be considered because this table gets updated frequently and I only want this to perform if there is a difference in quantity or price.

If I use the following: 

CREATE TRIGGER	SOMODAUDIT
ON		M2MDATA01.DBO.SORELS
AFTER		UPDATE,
		INSERT,
		DELETE
AS

SET NOCOUNT ON

DECLARE	@Action CHAR(1)

IF EXISTS (SELECT * FROM inserted)
	IF EXISTS (SELECT * FROM deleted)
		SET	@Action = 'U'		-- Update
	ELSE
		SET	@Action = 'I'		-- Insert
ELSE
	SET @Action = 'D'			-- Delete
  
INSERT		SOMODIFICATIONS.DBO.SOMODTEST
		(
			FENUMBER,
			FSONO,
			FRELEASE,
			FDATECHANGED,
			FORDERQTYOLD,
			FORDERQTYNEW,
			FUNETPRICEOLD,
			FUNETPRICENEW,
			PRICEMOD,
			MODTYPE
		)
SELECT		i.FENUMBER,
		i.FSONO,
		i.FRELEASE,
		GETDATE(),
		d.FORDERQTY,
		i.FORDERQTY,
		d.FUNETPRICE,
		i.FUNETPRICE,
		(i.FNETPRICE - d.FNETPRICE),
		@Action
FROM		inserted AS i
FULL JOIN	deleted AS d ON d.IDENTITY_COLUMN = i.IDENTITY_COLUMN
WHERE  I.FNETPRICE <> D.FNETPRICE
          OR I.FORDERQTY <> D.FORDERQTY


I don't get the inserts or deletes. I suspect it is because of the WHERE clause and the null values.

Is there anything I can do to keep the more elegant SQL Trigger but get my functionality at the same time?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-11 : 14:44:59
Change like this and try:-
CREATE TRIGGER	SOMODAUDIT
ON		M2MDATA01.DBO.SORELS
AFTER		UPDATE,
		INSERT,
		DELETE
AS

SET NOCOUNT ON

DECLARE	@Action CHAR(1)

IF EXISTS (SELECT * FROM inserted)
	IF EXISTS (SELECT * FROM deleted)
		SET	@Action = 'U'		-- Update
	ELSE
		SET	@Action = 'I'		-- Insert
ELSE
	SET @Action = 'D'			-- Delete
  
INSERT		SOMODIFICATIONS.DBO.SOMODTEST
		(
			FENUMBER,
			FSONO,
			FRELEASE,
			FDATECHANGED,
			FORDERQTYOLD,
			FORDERQTYNEW,
			FUNETPRICEOLD,
			FUNETPRICENEW,
			PRICEMOD,
			MODTYPE
		)
SELECT		i.FENUMBER,
		i.FSONO,
		i.FRELEASE,
		GETDATE(),
		d.FORDERQTY,
		i.FORDERQTY,
		d.FUNETPRICE,
		i.FUNETPRICE,
		(i.FNETPRICE - d.FNETPRICE),
		@Action
FROM		inserted AS i
FULL JOIN	deleted AS d ON d.IDENTITY_COLUMN = i.IDENTITY_COLUMN
AND  (I.FNETPRICE <> D.FNETPRICE
          OR I.FORDERQTY <> D.FORDERQTY)
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site