Sql Trigger Print Message

sql trigger print message

It is because of the way that triggers are run, basically it is not in your query execution window. One way of doing this is logging to the event viewer.

Create trigger TestTrigger on
tablefortrigger
for insert
as
–Declare variable to hold message
Declare @Msg varchar(8000)
–Assign to message “Action/Table Name/Time Date/Fields inserted
set @Msg = ‘Inserted | tablefortrigger | ‘ + convert(varchar(20), getdate()) + ‘ | ‘
+(select convert(varchar(5), track)
+ ‘, ‘ + lastname + ‘, ‘ + firstname
from inserted)
–Raise Error to send to Event Viewer
raiserror( 50005, 10, 1, @Msg)

Another way of doing this is to write to a file, there is of course permissions issues here, but you could do this:

Alter trigger TestTrigger on
tablefortrigger
for insert
as
Declare @Msg varchar(1000)
–Will hold the command to be executed by xp_cmdshell
Declare @CmdString varchar (2000)
set @Msg = ‘Inserted | tablefortrigger | ‘ + convert(varchar(20), getdate()) + ‘ — ‘
+(select convert(varchar(5), track)
+ ‘, ‘ + lastname + ‘, ‘ + firstname
from inserted)
–Raise Error to send to Event Viewer
raiserror( 50005, 10, 1, @Msg)
set @CmdString = ‘echo ‘ + @Msg + ‘ >> C:logtest.log’
–write to text file
exec master.dbo.xp_cmdshell @CmdString

More information can be found here: http://www.sql-server-performance.com/2005/log-file-trigger/

MySQL trigger: print a warning message when an inserted entry is greater than a value

Couple of things wrong here.

1. Delimiters. When you make a MySQL procedure or trigger, you need to be very explicit about delimiters so the query interpreter can distinguish between ends of lines in your procedure and the end of your declaration.
2. Location of the BEGIN statement. It should be directly after FOR EACH ROW.
3. Use of WHEN instead of IF.
4. Use of PRINT instead to SIGNAL SQLSTATE '...' SET MESSAGE_TEXT = '...'. This is how you raise exceptions in MySQL 5.5+.

Here is code that should work!

DELIMITER $$

CREATE TRIGGER testa_trig  
BEFORE INSERT ON testa 
FOR EACH ROW BEGIN
    IF (NEW.c > 100) THEN 
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning: c > 100!';
    END IF;
END$$

DELIMITER ;

Trigger to display message using PL/SQL

It won't work only if EMP_ID isn't larger than 0. Is it? In my case, it works:

Sample table:

SQL> CREATE TABLE employee
  2  (
  3     emp_id   NUMBER
  4  );

Table created.

Trigger:

SQL> CREATE OR REPLACE TRIGGER display_message
  2     AFTER INSERT OR UPDATE
  3     ON employee
  4     FOR EACH ROW
  5     WHEN (new.emp_id > 0)
  6  BEGIN
  7     DBMS_OUTPUT.put_line ('new employee details inserted');
  8  END;
  9  /

Trigger created.

Testing:

SQL> SET SERVEROUTPUT ON;
SQL> INSERT INTO employee (emp_id)
  2       VALUES (100);
new employee details inserted                 --> the message is here!

1 row created.

SQL>

How to read the content of PRINT statement of a trigger from SQL Server, and display it in a view?

I think you can't use "print" to return any value You have to use "select" instead:

SELECT 'AMOUNT MUST BE A VALUE BETWEEN 0 AND 50'

Then when run insert query, be sure that you execute commend by use ExecuteScalar.

But I don't prefer above solution, while you are using MVC4 please use data annotation as below :

On the class file of the Book, make use for data annotations:

   using System.ComponentModel.DataAnnotations;

And change the field declaration to :

  [Range(0,50,ErrorMessage="Amount must be between 0 and 50")]
   public int Amount { get; set; }

Now on the view add this after Amount field:

  @Html.ValidationMessageFor(model => model.Amount, "", new { @class = "text-danger" })

And in your controller you must check if the current ModelState is valid.

public ActionResult Post(CreateBook book)
{
    if (ModelState.IsValid) // this will check your model for validations like range, required and so on 
    {

    }
}

More reading here: https://msdn.microsoft.com/en-us/library/dd901590%28VS.95%29.aspx?f=255&MSPPError=-2147217396

And about validation using data annotations here: http://www.asp.net/mvc/overview/older-versions-1/models-%28data%29/validation-with-the-data-annotation-validators-cs

And get rid of that trigger. Non-validated data mustn't get throught your business to the database.

trigger's print statements show before rows affected by actual query

The trigger happens after the changes have been made to the target table, but in the same transaction, and before the success or failure of the DML has been reported to the client.

The trigger can throw, raiserror, or rollback to undo the change, in which case the client shouldn't see the DONE/DONEINPROC message that carries the rowcount, and should just get an error message instead.

SQL Server Management Studio - PRINT from trigger not showing

2 problems

1. Your trigger will cause a "too many recursions" error because it tries to update the table (within trigger) which calls the trigger again, which then tries to update the table, then calls the trigger... ad infinitum (in theory). In practice, SQL Server kills it after detecting the error.

2. The "Edit Top 200 rows" is a special feature that has its own messages window, so you cannot see any output. You need to run your code in a new Query window.

In a new query window, redefine the trigger as follows:

ALTER TRIGGER [dbo].[Trigger1]
ON [dbo].[TestTable]
FOR UPDATE
AS
    PRINT 'YAHOOOOO!!!'; /* can't see this message */
GO

Then, run this UPDATE statement on its own.

UPDATE TestTable
SET    TestData = 'testitem'
WHERE  TestId = 2;

Note: Drop the extraneous brackets.

---

Related Topics